Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table with Portfolio as rows and sum(sales) as columns.
I want that my table displays only rows with sales >1.000.000 and sales < -1.000.000
How can i obtain this result?
Hi AleCruc,
You can use set expression for filtering data.
Instead of using sum(sales), you should use sum({<Portfolio={"=sum(sales)>1000000"}*{"=sum(sales)<-1000000"}>} sales)
hi,
thank U for your answer but it does not work 😞
Two step solution:
1. Add to the script editor:
Load
Portfolio,
Sum(Sales) as TotalSales
Resident YourDataTableName
Group By Portfolio;
2. In your Sheet view create a Master Dimension with the formula:
=if(TotalSales<-1000000 or TotalSales>1000000 ,Portfolio)
and Name it something relevant (e.g. Portfolio (Limited) )
You can use the new fields in the Pivot:
This works as well, with + instead of * (make sure the field Sales is the correct case):
sum({<Portfolio={"=sum(Sales)>1000000"}+{"=sum(Sales)<-1000000"}>} Sales)
This works if you change * to +
sum({<Portfolio={"=sum(Sales)>1000000"}+{"=sum(Sales)<-1000000"}>} Sales)
ok, this could work but what the case if I want something more dynamic ?
i mean the 1million limitation was and example.. i expect something as an input box were i can write the interessed limitation conditions (that can change) and my pivot will shows me the result but without changing the total.
Is this possibile in some way?
Thanks
may be
sum({<Portfolio={"=sum(sales)>1000000 and sum(sales)< -1000000"}>} sales)