count stores that don't reach 80% sales goal

[José_Espinoza]

I'm trying to make something in Qlik Sense that allows me to show the number of stores that didn't reach the planned 80% sales goal.

First, I have a KPI displaying the number of stores that sold something:

Count({$<[Sales Amount]={">0"}>}  [Shop])

And I also know how to get the % goal of each store:

num(sum( [Sales Amount])/sum( [Sales Amount Planned]))

This returns a %.

But how can I get the number of shops who failed to meet at least 80% of planned sales? I tried some stuff with Set Analysis, but as I'm new with this, I don't know if this is right, but I do know it doesn't work:

count({< (sum([Sales Amount])/sum([Sales Amount Planned])) = {">0<=0.8"} >} [Shop])

So, how can I ge this working with set analysis? Or maybe set analysis isn't the answer for this kind of problem at all...

[sunny_talwar · Accepted solution]

May be this

Count(DISTINCT {$<Shop = {"=Sum([Sales Amount])/Sum([Sales Amount Planned]) < 0.8"}>}  [Shop])

[adityaakshaya]

Hi Jose,

Below is the expression which can help you consider you have Store as Dimension,

count(aggr( if(num(sum( [Sales Amount])/sum( [Sales Amount Planned]))<.8,1) Store))

Above expression can give # of store having less than 80% planned sales.

Regards,

Akshaya

PS - In place of "1" in the expression, you can write any expression.

[José_Espinoza]

This worked perfectly, thanks a lot! But, could you please explain me the logic behind it?

[sunny_talwar]

Using a search string against Shop, I am picking all those shops where it meets this condition

=Sum([Sales Amount])/Sum([Sales Amount Planned]) < 0.8

You can see this if you create a chart with Shop as dimension and the above expression. Make sure to uncheck 'Supress Zero Value' on the presentation tab. Now you will see a bunch of 0s and -1s. All those shops which have -1 will be shown by your main expression and the shops with 0 won't.