Interactively Hiding Pivot Rows

[Anonymous]

I have two slider objects set up that allow users to control Visual Cues in my two expression columns. This works great. But the users also want all the rows that fall outside of these cues (non-red values) to be hidden. So the rows displayed in the pivot would change as the user changes either of the slider values. Does anyone have any suggestions on how this might be accomplished?

[Anonymous]

I think a simple way is to modify all expressions, make them conditional, so they that they are =0 if at least one "red" condition is met.  And, use supress 0 values.

Regards,

Michael

[Anonymous]

I see what you mean. I'll give it a try.

[Anonymous]

It doesn't look like that's going to work since expressions 3 and 4 use expressions 1 and 2. It's a circular reference issue. Image one shows that it works for expressions 3 and 4 but image 2 shows what happens to expression 3 when I try to zero expression 1.

Image 1

Image 2

[Anonymous · Accepted solution]

Well, you have to break the circular reference in this case.  Can't tell without looking into the expressions, but the approach is not to refer directly to expressions 1 and 2 when you calculate 3 and 4. For example you calculate $ Difference as
[Base Period] - [Comaprison Period]
repalce it with:
<expression you use in Base Period without hide condition> - <expression you use in Base Period without condition>

I understand that the condition in Base and Comparion may look quite complex.  To make it look simpler, you can create additional expression "Condition" which is 0 if there is at least one red condition, and 1 otherwise.  In Expressions 1 and 2 - multiply by [Condition]

In general, if you clear understand the logic of what you need, you'll figure out the rest.

Regards,
Michael

[Anonymous]

I'm not quite there yet but you got me on the right track. Thanks!