Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following pivot table example. What I want to be able to do is exclude all Sales that are under 500. I'm using an expression like if(sum(sales) >= 500, sum(sales)). I'm able to get exclude all of the companies with < 500 sales but the total is including the total for all companies not just the ones over 500. I've also used aggr(sum(sales), entity) and have gotten the same results. Any suggestions?
Thanks for the help!
Your aggr() should be wrapped within an outer aggregation and needs to include all relevant dimensions. Therefore try it with:
sum(aggr(if(sum(sales) >= 500, sum(sales)), Customer, entity))
- Marcus
Your aggr() should be wrapped within an outer aggregation and needs to include all relevant dimensions. Therefore try it with:
sum(aggr(if(sum(sales) >= 500, sum(sales)), Customer, entity))
- Marcus
Or may be use the Set Expression like this ?
Sum(aggr(sum({<sales={"=sum(sales) >=500"}>}sales),Customer, entity))
If you want it as the example you sent, you have to do the following set analysis
IF(SUM({<SALES={">=500"}>}SALES)>=500,SUM({<SALES={">=500"}>}SALES),SALES)