How to suppress 0 in expression

[posywang]

Hi,

We have 4 expressions: Expense, Forecast, Actual, and Variance. We need to show values based on the months. For past and future months (months other than October), we have values in Expense; for current month (October), we have values in Forecast, Actual, and Variance. Is there a way to hide the Forecast, Actual, and Variance from past and future months, where values are all zero, and hide Expense from October, where values are zero? Thanks!

What we currently have:

What we want to achieve:

[sunny_talwar]

Is this a pivot table you are working with? If you are then there is not straight forward way to do this. The only way would be to use manipulate your dimensions so that you only have to use a single expression to get this result. This would require you to use Island tables

[swuehl]

You can create a dimension that encode your expression type (Forecast, Expense, Var and Actual) and link to your Month dimension accordingly (Expense link to all month except Oct, Oct link to all expression types except Expense).

Then use a single expression that checks the expression type dimension value and branch into your current expressions.

[posywang]

Thank you for your quick response. I put together a quick sample QVW and attached it to the original post. Can you please take a look and show me how to do it (Link month, create dimension, etc.)? Thanks!

[sunny_talwar · Accepted solution]

I added a script at the end of your load

Dim:

LOAD * Inline [

Dim

1

2

3

4

];

and then the pivot table with the following dimensions

Area

ResType

Month

=Pick(Dim, 'Expense', 'Forecast', 'Actual', 'Variance')

Single expression

Pick(Dim,

Sum(Expense),

Sum(Forecast),

Sum(Actual),

Sum([Forecast]-[Actual]))

to get this:

[posywang]

Thank you!!! It worked!!!! You are awesome!!!!!!

[jj_kobalt]

Is it possible to do the same trick, but with multiple dimensions at the top? When I do this I get only values at the last drillable dimension