topic Re: Aggr in a Chart in QlikView

Aggr in a Chart

Thu, 10 Nov 2016 17:13:49 GMT

Been awhile since I've been developing in Qlik and have been getting hung up on this expression.

I'm trying to show a line chart with the total percentage of Actual to Expected calls. If the Actual Calls is greater than the Expected, we just take the expected/expected so basically 1. But I want to show the aggregated percentage of all Physicians at the week end. See below.

IF(ACTUAL_CALLS>EXPECTED_CALL, Aggr(SUM(EXPECTED_CALL)/SUM(EXPECTED_CALL),PHYS_ID), AGGR(SUM(ACTUAL_CALLS)/SUM(EXPECTED_CALL),PHYS_ID))

Gives me No data in the chart below.

IF(ACTUAL_CALLS>EXPECTED_CALL, SUM(EXPECTED_CALL)/SUM(EXPECTED_CALL), SUM(ACTUAL_CALLS)/SUM(EXPECTED_CALL))

Gives me the total below but it's not given me the aggregated total by PHYS ID. 7/29 shows 100% but if you look in the source data that's not the case.

Re: Aggr in a Chart

johnw — Thu, 10 Nov 2016 17:26:22 GMT

So why is 100% wrong? Are we basically trying to limit actual calls to expected for each physician? So Dr. Smith is 2/2, Dr. Jones is 2/2, Dr. Bob is 1/3, and Dr. Sue is 2/2? So the total should be 7/9 = 78%?

Maybe this?

sum(aggr(rangemin(sum(ACTUAL_CALLS),sum(EXPECTED_CALL)),PHYS_ID))/sum(EXPECTED_CALL)

Re: Aggr in a Chart

johnw — Thu, 10 Nov 2016 17:30:51 GMT

Or if we'll only ever have one row per physician and week:

sum(aggr(rangemin(ACTUAL_CALLS,EXPECTED_CALL),PHYS_ID))/sum(EXPECTED_CALL)

Or if you need to do this in a chart where week is not a dimension:

sum(aggr(rangemin(ACTUAL_CALLS,EXPECTED_CALL),PHYS_ID,WK_ENDING))/sum(EXPECTED_CALL)

Re: Aggr in a Chart

Thu, 10 Nov 2016 17:38:00 GMT

Thanks for the help John. I am trying to show the total (i.e 7/9 78%) and it seems as if last expression worked in the chart to display across the different weeks. Thanks again.

Or if you need to do this in a chart where week is not a dimension:

sum(aggr(rangemin(ACTUAL_CALLS,EXPECTED_CALL),PHYS_ID,WK_ENDING))/sum(EXPECTED_CALL)