Show bar chart with sales where stores achieve at least 90% of target

[madnanansari]

I have made a bar chart before where I have to show Top 10 Stores for sales. The formula I used is as below:

Sum({<Store = {"=Rank(Sum(Sales))<=10"}>} Sales)

Now I am trying to find those stores which have achieved 90% of their targets and show there sales. I am trying the below formula which is not working:

Sum({<Store = {"=Sum(Sales)/Sum(Target) >=0.9"}>} Sales)

Pls. help what mistake I am making here.

[vinieme12]

TRy

Sum({<Store = {"=(Sum(Sales)/Sum(TOTAL <Store> Target)) >=0.9"}>} Sales)

or

Sum({<Store = {"=Sum(Sales) >= (Sum(Target)*0.9)"}>} Sales)

or

if(Sum(Sales)/Sum(Target) >=0.9,sum(Sales))

[MK_QSL]

This should work..

SUM({<Store = {"=SUM(Sales)/SUM(Target)>=0.9"}>}Sales)

If this is not working, better if you can provide sample data or your sample application to test.

[girirajsinh]

sum({<Store={"=sum(Sales)>=(sum(Target)*0.9)"}>} Sales)

It is same as what Vineeth suggested. 2nd one.  If you have Targets per Store.

[madnanansari]

none working.

[MK_QSL]

Provide sample data or sample application.

[paoloderegibus]

I have excluded the stores in two ways:

1. using the dimension limit tab
2. using an if statement in the dimension

Please take a look at the attached file. Is that what you wanted to achieve?

[madnanansari]

attached file?

[paoloderegibus]

yes, I have attached a file and I can see it in QlikCommunity (https://community.qlik.com/message/1198310?et=watches.email.thread#1198310).

by the way if you use this: "=if(aggr(sum(Sales)/sum(Target), Store)>=0.9, Store)" as you ber chart dimension it should work.