topic Re: Filter Dimension Values when Expression is Zero in QlikView

Filter Dimension Values when Expression is Zero

Fri, 11 Jul 2014 10:11:50 GMT

I would like that the calculated dimension's value do not appear in a pivot-table if the expression is 0 for this cell. For instance, I have a calculated dimension "Corrected Quarter". I would like that a Quarter do not appear in the calculated dimension if there is no revenue (0) for this quarter.

I give an example pivot-table below:

S1 and Q1 have no Revenues, and I would like that they do not appear in the calculated dimension at all.

Is there a way ?

Re: Filter Dimension Values when Expression is Zero

alexandros17 — Fri, 11 Jul 2014 10:18:27 GMT

In a pivot table if you need a row to disappear set the exclude zero values is not enough because all the expression for that row must be 0

in this case, if (for example) the expression that return 0 is : sum(myval) then all the other columns must test that expression and become:

if (sum(myval)=0, 0, ... the real column expression)

Let me know

Re: Filter Dimension Values when Expression is Zero

Fri, 11 Jul 2014 10:21:22 GMT

In my case this is

=IF(Sum(Revenue)=0,Null(),Year & '-' & pick(match(UPPER(Month),'JAN','FEB','MAR','APR','MAY','JUN', 'JUL','AUG','SEP','OCT','NOV','DEC'), '01','02','03','04','05','06','07','08','09','10','11','12'))

and returns Error in Calculated Dimension : (

I enabled conditional

Do you have an idea ?

Thanks for your reply

Re: Filter Dimension Values when Expression is Zero

Fri, 11 Jul 2014 10:22:34 GMT

Try to use either "Enable condition" or "Suppress When Value is Null"

Regards

Vinay

Re: Filter Dimension Values when Expression is Zero

sasikanth — Fri, 11 Jul 2014 10:32:02 GMT

try some thing like this

if(sum(Reveue)>0,QUARTER)