Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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)
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)
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)