Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
desertmatt
Contributor III
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

14 Replies
sunny_talwar

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

sunny_talwar

Alternatively you don't you create a pivot table with Region as a dimension and then 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({<SaPcs= {">=$(MinCount)"}>}SaPcs))

and enable partial sum for Region field on the properties tab

desertmatt
Contributor III
Contributor III
Author

Thank you very much Sunny both work - also Pivot looks promising (need to play more with it...)

While tweaking now the charts a new thought came up how to tackle this bestseller list - maybe you can help here too?

If I stick with the first chart format (not pivot) - and leave also some columns with 0 values - how could I create instead of the Total column and all the If conditions a Count column which counts in how many regions a Product had sales - e.g. if Product A sold in Asia and Europe, Count would show 2 (like a rank) if in all 3 Regions then 3 etc.

How could I get this?

Thanks a lot

Matt

sunny_talwar

There might be an easier expression to do this, but this should work also

If(Sum({<Region= {"Asia"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0, 1, 0) +

If(Sum({<Region= {"Europe"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0, 1, 0) +

If(Sum({<Region= {"Middle East"}, SaPcs= {">=$(MinCount)"}>}SaPcs) > 0, 1, 0)

desertmatt
Contributor III
Contributor III
Author

Thanks again Sunny will try that and see how it goes...

kind regards

Matt