topic Re: How to suppress 0 in expression in QlikView

How to suppress 0 in expression

posywang — Tue, 08 Nov 2016 18:53:05 GMT

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:

Re: How to suppress 0 in expression

sunny_talwar — Tue, 08 Nov 2016 19:08:42 GMT

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

Re: How to suppress 0 in expression

swuehl — Tue, 08 Nov 2016 19:10:06 GMT

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.

Re: How to suppress 0 in expression

posywang — Tue, 08 Nov 2016 21:03:23 GMT

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!

Re: How to suppress 0 in expression

sunny_talwar — Tue, 08 Nov 2016 22:34:27 GMT

I added a script at the end of your load

Dim:

LOAD * Inline [

Dim

];

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:

Re: How to suppress 0 in expression

posywang — Tue, 08 Nov 2016 23:52:14 GMT

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

Re: How to suppress 0 in expression

jj_kobalt — Tue, 07 Apr 2020 08:28:15 GMT

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