Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

desertmatt
New Contributor III

Show only products which have Min Sales in all country columns

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

Bestseller.png

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Show only products which have Min Sales in all country columns

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

14 Replies

Re: Show only products which have Min Sales in all country columns

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

desertmatt
New Contributor III

Re: Show only products which have Min Sales in all country columns

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

Re: Show only products which have Min Sales in all country columns

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

desertmatt
New Contributor III

Re: Show only products which have Min Sales in all country columns

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

Re: Show only products which have Min Sales in all country columns

Your expression should this for initiate

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

MVP
MVP

Re: Show only products which have Min Sales in all country columns

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
New Contributor III

Re: Show only products which have Min Sales in all country columns

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)

MVP
MVP

Re: Show only products which have Min Sales in all country columns

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
New Contributor III

Re: Show only products which have Min Sales in all country columns

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

Community Browser