Calculate Pivot Tables subtotals by sum(row)

[Anonymous]

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!

[marcus_sommer · Accepted solution]

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

[jayanttibhe]

Or may be use the Set Expression like this ?

Sum(aggr(sum({<sales={"=sum(sales) >=500"}>}sales),Customer, entity))

[jorgecelis]

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)