Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
shamama_iqbal
Partner
Partner

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

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:

shamama_iqbal_0-1603459988374.png

Expected Output in Pivot table:

shamama_iqbal_1-1603460022601.png

Many thanks in advance!

Regards,

Shamama

 

Labels (3)
3 Replies
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,....

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Kushal_Chawda

@shamama_iqbal  Is #Employee field in data or expression?

simon20
Partner
Partner

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