Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
José_Espinoza
Partner - Contributor III
Partner - Contributor III

count stores that don't reach 80% sales goal

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...

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

4 Replies
sunny_talwar

May be this

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

adityaakshaya
Creator III
Creator III

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
Partner - Contributor III
Partner - Contributor III
Author

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.