topic Re: Set Analysis - overall sum by month in QlikView

Set Analysis - overall sum by month

byrnel0586 — Wed, 18 Oct 2017 20:19:29 GMT

Hi,

Please see the table below:

For the Num column I am currently using this expression to sum the PHARM_NUM_STATUS based on selections and to show a YOY comparison and it is working.

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

But now for the CP Num column I am needing to show the overall sum of all data as a comparison, even when a selection is made. I tried

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

but this is summing all PHARM_NUM_STATUS for the entire year.

Any help is appreciated.

Re: Set Analysis - overall sum by month

sunny_talwar — Wed, 18 Oct 2017 22:39:17 GMT

May be this

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

Re: Set Analysis - overall sum by month

byrnel0586 — Thu, 19 Oct 2017 12:06:18 GMT

Thank you, but that is returning 0's for both 2016 and 2017.

Re: Set Analysis - overall sum by month

sunny_talwar — Thu, 19 Oct 2017 12:18:46 GMT

Would you be able to share a sample to look at this?

Re: Set Analysis - overall sum by month

byrnel0586 — Fri, 20 Oct 2017 12:41:13 GMT

Sure, here you go. The total CP Num for 2016 should be 117,483 and for 2017 = 117,199. But, I would like those numbers to change based on the type (Diabetes, RAS, Statin) and months chosen. Thank you!!

Re: Set Analysis - overall sum by month

sunny_talwar — Fri, 20 Oct 2017 12:46:24 GMT

May be this

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

Re: Set Analysis - overall sum by month

byrnel0586 — Fri, 20 Oct 2017 15:10:10 GMT

Thank you, it is working! I am now having an issue I think with the Star value.... You can see for the 2017 Star it is saying 4, but the rate = 89.37% and to the right you can see the STAR 5 threshold is 89%, so it should be a 5... I am not sure why it is reading this wrong. I've attached a new app so you can see what I mean.

Re: Set Analysis - overall sum by month

sunny_talwar — Fri, 20 Oct 2017 15:14:59 GMT

When I open your dashboard, I see this

Which selections bring me to those results 89.37% and 4?

Re: Set Analysis - overall sum by month

byrnel0586 — Fri, 20 Oct 2017 15:20:00 GMT

Grouper Name = Navarro, Type = Diabetes, Month = 8, and Year = 2017. Then you can see down in the YOY table the 89.37% Rate and 4 Star value. But the list boxes to the right show the STAR 5 threshold being 89%, so the Star should = 5 instead of 4.

Re: Set Analysis - overall sum by month

sunny_talwar — Fri, 20 Oct 2017 15:38:41 GMT

Same selection as you mentioned shows me 89.81% for CP Rate and 5 for CP Star

Re: Set Analysis - overall sum by month

byrnel0586 — Fri, 20 Oct 2017 16:18:27 GMT

Oh, I am talking about the Star value, not CP Star - that seems to be working. Below are the columns. But they should still point to the same thresholds as the CP Star value.

Re: Set Analysis - overall sum by month

sunny_talwar — Fri, 20 Oct 2017 16:33:11 GMT

You seem to be using a old version of Rate % in your star expression... just updated that and it seems to be working

Re: Set Analysis - overall sum by month

byrnel0586 — Fri, 20 Oct 2017 16:36:19 GMT

I'm so sorry, my mistake! Thank you so much for your help!