3 Replies Latest reply: Nov 10, 2016 12:38 PM by Nate Eyster RSS

    Aggr in a Chart

    Nate Eyster

      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.

       

      test.png

       

      test2.png

        • Re: Aggr in a Chart
          John Witherspoon

          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
              John Witherspoon

              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
                  Nate Eyster

                  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)