How to calculate maximum month for each quarter and for year from the available data set

[shamama_iqbal]

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

[Taoufiq_Zarra]

@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,....

[Kushal_Chawda]

@shamama_iqbal  Is #Employee field in data or expression?

[simon20]

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])