Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the below table with the data. Can anyone please help me to write the set analysis expression for the expected output.
Data Set:
Expected Output in Pivot table:
Many thanks in advance!
Regards,
Shamama
@shamama_iqbal May be you can use Pivot table with Category, Year and Quarter as dimension
and sum(#Emplmoye) as Measure and after pivot the Year and Quarter to column
Or if you want use Set analysis you can for Q1,2019 add measure
sum({<Quarter={'Q1',Year={'2019'}}>}[#Employe])
same for Q2,....
@shamama_iqbal Is #Employee field in data or expression?
If you have access to the script you can add a table to the calendar with flags like "_End_Of_Quarter". Like this:
LOAD
Month,
1 AS _End_Of_Quarter
RESIDENT ....
WHERE Mod(Num(Month),3)=0;
Then the expression would be:
Sum({<_End_Of_Quarter={1}>} [#Employee])