Show only products which have Min Sales in all country columns

[desertmatt]

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

[Anil_Babu_Samineni]

May be look Dimensionality() concept in Qlikview or use Partial sum in pivot table?

[desertmatt]

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

[Anil_Babu_Samineni]

May be i misread the thread. Will you provide short question where you need looking to work?

[desertmatt]

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

[Anil_Babu_Samineni]

Your expression should this for initiate

Sum({<Region= {'Asia','Europe','Middle East'}, SaPcs ={">=$(MinCount)"}>}SaPcs)

[sunny_talwar]

You are just getting the total row and not the rows that make up the total? Really? can you share your app if possible?

[desertmatt]

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)

[sunny_talwar]

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

[desertmatt]

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