2 Replies Latest reply: Jun 20, 2017 7:37 PM by Peter Bower RSS

    Sum or Avg based on Dimension value

    Peter Bower

      Hi there,

      I'm working in Qlik Sense, and finally go to through ETL process from multiple sources to aggregate and store a bunch of KPI's by month, team member and team.

      Im now working in Sense and want to do charts, but having a few issues. Would really appreciate any help.

       

      1. What is the best way so that I can 'sum' certain KPI's but 'avg' others when aggregating across multiple month(s)? eg. some KPI's I want to do an avg total, others a sum total.

      2. How do I display %'s on the graphs for certain KPI's, but leave others as whole no's? The auto is doing a bad job...

      3. How do I force the graphs not to display anything unless a user is selected in the filter pane? Reason:  I want it to be an 'Individual Dashboard' without it rolling up to Overall - so only users aren't compared and not their combined scores.

      Thanks for your help.

      Peter

        • Re: Sum or Avg based on Dimension value
          Sada Siva

          Hi Peter,

           

          maybe like this,

          For point 1 - It dependson on the dimension type which  are using in the chart. If it is not a time dimension then your aggr numbers by month wil give  more accurate numbers.

           

          For Point 2 - Take a 'combo Chart type' and use the primary and secondary axis. one you can show the %'s and other for numbers.

           

          For Point 3 - use the <min(time_dim) in expression. by default it will show 0. if you select time filter then it will gives you the numbers.

            • Re: Sum or Avg based on Dimension value
              Peter Bower

              Hi Sada,

              Thanks for your response.

              Unfortunately these do not seem to be achieving the desired outcomes/

              Pt. 1 - I am displaying a number of months , and then 'results' across these months in the chart. When I filter the chart using a certain KPI (by filtering), I want to make sure they sum the KPI, others I want to Avg.

               

              Pt. 2. - This seems to suggest I need to setup KPI's that are %'s as a different Dimension, and KPI's that are no.'s as their own dimension, rather than just having the KPI name as a field that can be filtered to change which KPI is on the graph? Isn't there a better way, so I can just 'filter' and it correctly show %'s or No.'s?

               

              Pt.3 - I tried this - however when filtering it still shows 0.

               

              Thanks


              Peter