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))
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
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
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)
Thanks again Sunny will try that and see how it goes...
kind regards
Matt