topic Re: Set Analysis - sum max month value in QlikView

Set Analysis - sum max month value

byrnel0586 — Wed, 23 Aug 2017 17:24:01 GMT

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.

Re: Set Analysis - sum max month value

vishsaggi — Wed, 23 Aug 2017 17:38:45 GMT

Try this may be:

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

Re: Set Analysis - sum max month value

byrnel0586 — Wed, 23 Aug 2017 17:48:49 GMT

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

Re: Set Analysis - sum max month value

vishsaggi — Wed, 23 Aug 2017 17:59:22 GMT

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)

Re: Set Analysis - sum max month value

byrnel0586 — Wed, 23 Aug 2017 18:11:47 GMT

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

sunny_talwar — Wed, 23 Aug 2017 18:19:40 GMT

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

Re: Set Analysis - sum max month value

byrnel0586 — Wed, 23 Aug 2017 18:25:28 GMT

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

sunny_talwar — Wed, 23 Aug 2017 18:29:28 GMT

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

Re: Set Analysis - sum max month value

byrnel0586 — Wed, 23 Aug 2017 18:30:59 GMT

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

sunny_talwar — Wed, 23 Aug 2017 18:32:47 GMT

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)

Re: Set Analysis - sum max month value

byrnel0586 — Wed, 23 Aug 2017 18:39:38 GMT

Thanks. For some reason it is working for 2016 but showing 0 for 2017...

Re: Set Analysis - sum max month value

sunny_talwar — Wed, 23 Aug 2017 18:41:32 GMT

Without the changes it was working for both 2017 and 2016?

Re: Set Analysis - sum max month value

byrnel0586 — Wed, 23 Aug 2017 18:45:31 GMT

Yes, it was working but changing with month selections. I think somewhere in the new expression it's pointing to the max month of all years rather than pivoting by each year? Makes sense to work for 2016 that way since the max month is 12 and max month for 2017 is only 7.

Re: Set Analysis - sum max month value

sunny_talwar — Wed, 23 Aug 2017 18:53:51 GMT

Try this

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

Re: Set Analysis - sum max month value

sunny_talwar — Wed, 23 Aug 2017 18:55:46 GMT

I wonder why this wouldn't work?

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

Re: Set Analysis - sum max month value

byrnel0586 — Wed, 23 Aug 2017 19:07:40 GMT

This one still shows 0 for 2017.

Re: Set Analysis - sum max month value

byrnel0586 — Wed, 23 Aug 2017 19:08:11 GMT

This one is giving an error

Re: Set Analysis - sum max month value

sunny_talwar — Wed, 23 Aug 2017 19:09:52 GMT

My bad, remove the inner double quotes

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

Re: Set Analysis - sum max month value

byrnel0586 — Wed, 23 Aug 2017 19:19:30 GMT

Thank you. This one is now pulling the sum for month 7 for both 2016 and 2017

Re: Set Analysis - sum max month value

byrnel0586 — Wed, 23 Aug 2017 20:21:21 GMT

Any other ideas? If you can't figure it out it must not be possible.