topic Re: Set Analysis! What the heck, part II? in QlikView

Set Analysis! What the heck, part II?

dpstanley_13 — Mon, 28 Aug 2017 17:34:51 GMT

Okay, so I'm back. I had this resolved until I had to make it interactive with the rest of my data. I have struggled with this all weekend, so I'm throwing this back out for assistance.

I need to have the "Median" and "stdev" columns calculate for each "Intervention" for the period of time defined by the last two columns of the table; "vMeasureInterventionCDStart" and "vMeasureInterventionCDEnd".

So, for the "Median", the value for "BASE" should be 14.8% and the value for "A" should be 10.6%.

I am attaching the app.

Re: Set Analysis! What the heck, part II?

sunny_talwar — Mon, 28 Aug 2017 18:32:55 GMT

Try this

=Num(Median(TOTAL <MeasureInterventionCD> Aggr((Sum({<CommonMeasureNM=, CalculationTypeDSC = {'Rate'}, IsNumeratorFLG = {1}>} If(MI_YearMonth >= $(vMeasureInterventionCDStart) and MI_YearMonth <= $(vMeasureInterventionCDEnd), MeasureValue))/

Sum({<CommonMeasureNM=, CalculationTypeDSC = {'Rate'}, IsDenominatorFLG = {1}>} If(MI_YearMonth >= $(vMeasureInterventionCDStart) and MI_YearMonth <= $(vMeasureInterventionCDEnd), MeasureValue))),YearMonthCD, MeasureInterventionCD)),'#,##0.0%')

Re: Set Analysis! What the heck, part II?

dpstanley_13 — Mon, 28 Aug 2017 18:54:36 GMT

Once again, you saved the day! Thanks!

Re: Set Analysis! What the heck, part II?

dpstanley_13 — Mon, 28 Aug 2017 19:05:19 GMT

Okay, I still have a problem. I forgot to mention that I need to convert this back to a combo chart. I was so excited that you answered so quickly, I did not see that you had added a second dimension. This is the final outcome I need. I only converted it to a table so that it was easier to see the values. My apologies.

Re: Set Analysis! What the heck, part II?

sunny_talwar — Mon, 28 Aug 2017 19:09:38 GMT

Try this

=Num(Aggr(Median(TOTAL <MeasureInterventionCD> Aggr((Sum({<CommonMeasureNM=, CalculationTypeDSC = {'Rate'}, IsNumeratorFLG = {1}>} If(MI_YearMonth >= $(vMeasureInterventionCDStart) and MI_YearMonth <= $(vMeasureInterventionCDEnd), MeasureValue))/

Re: Set Analysis! What the heck, part II?

sunny_talwar — Mon, 28 Aug 2017 19:11:13 GMT

This

Re: Set Analysis! What the heck, part II?

dpstanley_13 — Mon, 28 Aug 2017 19:16:08 GMT

Awesome!