Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
Am trying to create a chart with Product NO, Total Sales (only for products which have SalesPcs greater or equal to/than Variable "MinCount" in ALL 3 global regions) - if I say set the MinCount variable to 1 (input box) , the chart should show only products which have in all 3 Regional columns minimum 1 pcs sold - the chart should show then the ProductNo, the Total of the Sums of the 3 columns, and the Sum of pcs sold in each region (column).
Dim = ProductNO, Expression = TotalPcs, Asia, Europe, Middle East
As expression for TotalNo I have: Sum({<Region= {"Asia"}, Region= {"Europe"}, Region= {"Middle East"}, SaPcs ={">=$(MinCount)"}>}SaPcs)
and for Asia: Sum({<Region= {"Asia"}, SaPcs= {">=$(MinCount)"}>}SaPcs) (same for the other 2 regions)
But I just get the totals as above and not the rows with bestseller products?
Any help would be highly appreciated
Thank you very much
Matt
For total, try this
If(Sum({<Region= {"Asia"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Europe"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Middle East"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0, Sum({<Region= {"Middle East", "Asia", "Europe"}, SaPcs= {">=$(MinCount)"}>}SaPcs))
May be look Dimensionality() concept in Qlikview or use Partial sum in pivot table?
Thanks Anil,
but how should this work? I read the qlikview help for this but don't understand ho to a this for my problem?
Could you please elaborate a little bit more - or point me to more detailed threads about dimensionality?
Kind regards
Matt
May be i misread the thread. Will you provide short question where you need looking to work?
I thought my question and screen shot above were pretty clear..
How can I show all product numbers of the products only which have in ALL 3 regions Sales >=1
With the expressions I show in my first mail - I get only the totals - which you see in the screen shot...
Thanks Matt
Your expression should this for initiate
Sum({<Region= {'Asia','Europe','Middle East'}, SaPcs ={">=$(MinCount)"}>}SaPcs)
You are just getting the total row and not the rows that make up the total? Really? can you share your app if possible?
Hello Anil and Sunny - thanks so far
sorry can't provide app as connected to server odbc with pw etc.
Now I got data but as you can see many values are 0 in certain columns (regions) but it should only show product numbers where all 3 regions have sales (SaPcs >= Mincount wher MinCount variable is set to 1 at the moment)
May be use if statements
If(Sum({<Region= {"Asia"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Europe"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Middle East"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0, Sum({<Region= {"Asia"}, SaPcs= {">=$(MinCount)"}>}SaPcs))
If(Sum({<Region= {"Asia"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Europe"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Middle East"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0, Sum({<Region= {"Europe"}, SaPcs= {">=$(MinCount)"}>}SaPcs))
If(Sum({<Region= {"Asia"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Europe"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Middle East"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0, Sum({<Region= {"Middle East"}, SaPcs= {">=$(MinCount)"}>}SaPcs))
Thanks Sunny - this worked but for the TOTAL column I created this super long line - is there a more elegant way to solve this?
(If(Sum({<Region= {"Asia"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Europe"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Middle East"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0, Sum({<Region= {"Asia"}, SaPcs= {">=$(MinCount)"}>}SaPcs)))+ (If(Sum({<Region= {"Asia"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Europe"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Middle East"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0, Sum({<Region= {"Europe"}, SaPcs= {">=$(MinCount)"}>}SaPcs)))+(If(Sum({<Region= {"Asia"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Europe"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0 and Sum({<Region= {"Middle East"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0, Sum({<Region= {"Middle East"}, SaPcs= {">=$(MinCount)"}>}SaPcs)))