Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Set Analysis - sum max month value

Hi all,

I am needing to sum a value (PHARM_NUM_STATUS) for only the max month of the year. I am currently using the below expression, but it is summing the values for all months of the year.

Sum({<PHARM_Year,PHARM_MonthNum>}PHARM_NUM_STATUS)

How can I modify this to only sum the max(PHARM_MonthNum) PHARM_NUM_STATUS?

Thank you in advance.

1 Solution

Accepted Solutions
Highlighted

Try this

=Sum({<PHARM_MonthNum, PHARM_Year>}Aggr(If(Only({1}PHARM_MonthNum) = Max({1} TOTAL <PHARM_Year> Aggr(Only({1}PHARM_MonthNum), PHARM_MonthNum, PHARM_Year)), Sum({<PHARM_MonthNum, PHARM_Year>} PHARM_NUM_STATUS)), PHARM_MonthNum, PHARM_Year))

View solution in original post

23 Replies
Highlighted
Champion III
Champion III

Try this may be:

= Sum({< PHARM_MonthNum = {"$(= Max(PHARM_MonthNum)) "} >}PHARM_NUM_STATUS)

Highlighted
Creator
Creator

Thank you, but that is returning zero. My dimension is PHARM_Year if that is useful.

Highlighted
Champion III
Champion III

So what is this PHARM_MonthNum? May be use this if not can you share a sample to look into?

= Sum({< PHARM_Year, PHARM_MonthNum = {"$(= Max(PHARM_MonthNum)) "} >}PHARM_NUM_STATUS)

Highlighted
Creator
Creator

That still shows as 0.

PHARM_MonthNum is the month that I want to see max value for. I do not have a sample app, but I have attached a screenshot of an example. Month 7 is max month, and the NUM = 123. That is the value I would like to see in the pivot chart below where the Num = 0. I need the expression to always point at max month with any selection.

Highlighted

Your chart dimension is Year to Date... is this different from Year field?

Highlighted
Creator
Creator

No, I just renamed the Year label as Year to Date. I tried what you posted a few minutes ago and it works, but changes when I select another month. Is there a way to keep the values static to the max month only?

Highlighted

Can you post what I added again... I can tell you what changes you can make with it

Highlighted
Creator
Creator

Sure.

Sum({<PHARM_Year, PHARM_MonthNum = {"=PHARM_MonthNum  = Max(TOTAL <PHARM_Year> Aggr(PHARM_MonthNum, PHARM_MonthNum, PHARM_Year))"}>} PHARM_NUM_STATUS)

 

Highlighted

Try this

Sum({<PHARM_Year, PHARM_MonthNum = {"=Only({1} PHARM_MonthNum) = Max({1} TOTAL <PHARM_Year> Aggr(Only({1} PHARM_MonthNum), PHARM_MonthNum, PHARM_Year))"}>} PHARM_NUM_STATUS)