Create pivot table displaying date column into years and for current year into quarters

Help Studio
, , , , ,
1

Hi all,

Can anyone please help me @ppr @NIVED_NAMBIAR @sanjay21051990 @Yoichi in creating a pivot table taking columns from date column and display data year wise for all prev years and current year with quarter wise using linq or vb.net. Below I have attached sample excel for reference.
ex.xlsx (10.2 KB)

Thanks in advance…

2

@Gattu_Monika
For limited time reasons find shared part result.

Your output excel is not complete tabularly. So it’s better to work on immediate results, allowing to get used to generate other results.

dtData:
grafik

PartResult grouping on EmpID, Year and Quarter and doing the value count and sum:
grafik

was done with the following LINQ:

(From d In dtData.AsEnumerable
Let dp = DateTime.ParseExact(d("Date").toString.Trim,"dd.MM.yyyy", CultureInfo.InvariantCulture)
Let ky = dp.Year
Let kq = Math.Ceiling(dp.Month / 3)
Group d By k1=d("EmpID"), k2=ky, k3=kq Into grp = Group
Let gsm = grp.Sum(Function (s) CInt(s("Value").toString.Trim))
Let cnt = grp.Count
Let ra = New Object(){k1,k2,k3, cnt, gsm}
Order By k2 , k3
Select r = dtPreResult.Rows.Add(ra)).CopyToDataTable

For calculating the min/max Year & Quarter we can do it with the help of tuples and an Int number concatenation trick:

(From t In dtPreResult.AsEnumerable.Select(Function (x) Tuple.Create(CInt(x("Year").toString.Trim), CInt(x("Quarter").toString.Trim)))
Order By CInt(t.Item1 & t.Item2)
Select tp=t).Distinct().toList

grafik

Just give it a try on implementing the next things. For the next iteration please let us know, on how to handle 2020 (aggregate all Quarters or have it expanded as well)

Find starter help here:
pprLINQBox_GattuMonika_Pivot.xaml (9.4 KB)

Also, have a look at this activity

1 Like
3

Hi @ppr ,
Thanks for your reply,
The requirement is to project the input data in pivot table according to year wise.
For current year(2021) it should show the data in quarter wise(Q1(Jan-Mar),Q2(Apr-Jun),Q3,Q4) as shown in output sheet. For all the prev years (ex 2020,2019,2018) it should project consolidated year wise(no need of quarter wise)… Thanks

ex1
ex1886×113 2.2 KB

exinput