Reduce data based on expression in a pivot table

[newhere2014]

Hi all,

  I have a pivot table with 5 dims and 5 expressions. What I want to achive is to reduce data based on one of the expressions. For example, I only want to show records in this table with Value1 < Expression1 < Value2.

  Right now, the method I used is to have a very complex expression on Dimension 1. If data is in this value range, show records; if not, Dimension1 show null, then select "suppress when value is null". This method works well, but the performance is terrible.

  I am wondering if anyone knows a better solution which won't affect performance much, or a way to improve performance.

Many thanks!

[Oleg_Troyansky]

Depending on the nature of your specific expression and your specific condition, you might be able to get the same result with a very complex Set Analysis condition.

First, determine what Data Element constitutes the single "record" that you want to show or hide. It could be "Customers", "Items", Orders, etc... Hopefully you can settle on a single field for that purpose. For this example, let's assume that it's "CustomerID".

Then, formulate the condtion that should determine whether to show the Customer or not. For simplicity, let's assume it's the total Sales per Customer. Let's say you want to show customers with Sales between 1000 and 3000. In this case, your Set Analysis would look like this:

sum( {<CustomerID={"=Sum(Sales)>=1000<=3000"}>} ... rest of your expression)

cheers,

Oleg Troyansky

[newhere2014]

Good idea.

I can use variables to replace 1000 or 3000, right? since users need to be allowed to input the value range.

I'll test it. And I'll get back to you if the performance gets improved or not.

Many thanks.

[newhere2014]

Hi Oleg,

  I modified expressions to :  sum({$<Style={"=sum(ACTUAL_COST)>=0<=50"}>} ACTUAL_COST), according to your example, did not get any data.

When I try sum({$<Style={"=sum(ACTUAL_COST)<=50"}>} ACTUAL_COST) or sum({$<Style={"=sum(ACTUAL_COST)>=0"}>} ACTUAL_COST), both works.

Could you help me check why it does  not work when using >=0<=50  together?

Meanwhile, I used sum({$<Style={"=sum(ACTUAL_COST)<=50"}>} ACTUAL_COST) to reduce data, but it did not work. Could you build an example for me?

thanks.

[Oleg_Troyansky]

perhaps using both conditions together this way doesn't work - just replace it by 2 conditions spelled out completely:

sum(ACTUAL_COST)<=50 and sum(ACTUAL_COST)>=0

sorry for the initial confusion, I was quoting from memory...