topic Re: Help with doing avg totals in table as a KPI Card in App Development

Help with doing avg totals in table as a KPI Card

MassicotPSCU — Mon, 15 May 2023 13:31:02 GMT

Hi everyone,

I've seen similar post like this but I'm not sure the solution provided is applicable to my expression. The solution requires set analysis but I'm not sure if or how to use set analysis to filter for nulls.

I have this table:

I also have this expression:

=if(IsNull(avg([Score])),1,AVG([Score]))

When sales value is blank a score of 1 is returned for the given name. My average in the total score row represents a value of 1.6 which is accurate. However, when I use this same expression in a KPI card I only get the average for the names with a populated sales value. I need the average for all names including the ones without a sales value.

Is there any way to directly transfer that value in the total's rows to a KPI chart or maybe fix my expression?

Here is a similar post with a solution using set analysis and aggr() but it does not use set analysis to filter for nulls.

Solved: Totals in a table as a KPI - Qlik Community - 1383348

Re: Help with doing avg totals in table as a KPI Card

Chanty4u — Mon, 15 May 2023 14:55:50 GMT

Try this

=if(IsNull(avg(Total <Name> [Score])), 1, AVG(Total <Name> [Score]))

Re: Help with doing avg totals in table as a KPI Card

MassicotPSCU — Mon, 15 May 2023 17:18:16 GMT

Hi thank you for your response. Unfortunately, I tried this, but I still get back the same incorrect result where it's only averaging the names with scores/sales.

Re: Help with doing avg totals in table as a KPI Card

Kushal_Chawda — Mon, 15 May 2023 19:57:50 GMT

@MassicotPSCU try below expression in KPI

=rangesum(Count(distinct{<Name={"=sum(Sales)=0"}>}Name), sum(Score))/ count(distinct Name)

Re: Help with doing avg totals in table as a KPI Card

MassicotPSCU — Wed, 17 May 2023 12:05:33 GMT

Thank you so much @Kushal_Chawda !