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

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)

@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:

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

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
``````

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
``````

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

Hi @ppr ,