Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

byrnel0586
Contributor

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

Re: Set Analysis - sum max month value

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

23 Replies
vishsaggi
Esteemed Contributor III

Re: Set Analysis - sum max month value

Try this may be:

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

byrnel0586
Contributor

Re: Set Analysis - sum max month value

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

vishsaggi
Esteemed Contributor III

Re: Set Analysis - sum max month value

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)

byrnel0586
Contributor

Re: Set Analysis - sum max month value

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.

Re: Set Analysis - sum max month value

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

byrnel0586
Contributor

Re: Set Analysis - sum max month value

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?

Re: Set Analysis - sum max month value

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

byrnel0586
Contributor

Re: Set Analysis - sum max month value

Sure.

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

 

Re: Set Analysis - sum max month value

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)

Community Browser