limiting pivot

[AleCruc94]

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?

[SerhanKaraer]

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)

[AleCruc94]

hi, 

thank U for your answer but it does not work 😞

[Anonymous]

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:

• Portfolio (Limited) with Sum(TotalSales) as a measure.

[Anonymous]

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)

[Anonymous]

This works if you change * to +

sum({<Portfolio={"=sum(Sales)>1000000"}+{"=sum(Sales)<-1000000"}>} Sales)

[AleCruc94]

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 

[Kushal_Chawda]

may be

sum({<Portfolio={"=sum(sales)>1000000 and sum(sales)< -1000000"}>} sales)