Sum or Avg based on Dimension value

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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