topic Re: supress Null for this year or pre year in the straight table in QlikView

supress Null for this year or pre year in the straight table

kishorj1982 — Wed, 25 Nov 2015 20:38:11 GMT

Hi All,

Please look in this sceanrio :

I have a Straight table where i use to compare this years values to the previous year in the expression - as

((This year value/ pre Year value )-1) - if this year values are NULL then it throws -1 as a out come. and if pre year values are null then, NULL.

I want to ignore these conditions from my straight table. How to do it ?

Suppress Null on expression tab doesn't work in this case.

Re: supress Null for this year or pre year in the straight table

JonnyPoole — Wed, 25 Nov 2015 21:07:40 GMT

it will depend if the values are 0 , blank or truly null

An example for Null checking as follows ( but first check if you can capture the values you want with isnull() or =0 etc...)

if( not

(

isnull( sum( {<Year={last year}>} Sales )

isnull( sum( {<Year={<this year>}>} Sales)

) ,

sum( {<Year={<this year>}>} Sales)/ sum( {<Year={<last year>}>} Sales)

)

Re: supress Null for this year or pre year in the straight table

kishorj1982 — Thu, 26 Nov 2015 01:23:47 GMT

Thanks Jonathan.

There are 2 scenarios :

For the Dimension - I dont have this year values (No rows at all) - Only previous year values - So

(this year / pre year)-1 calculated as (0/ Non zero values ) -1 = -1

For the Dimension - If I dont have pre year values (No rows at all) - Only this year values - So

(this year / pre year)-1 calculated as ( Non zero values / 0 ) -1 = NULL

Chart shows both these expression values as I have last column where I am sum up This Year Sells

Supress 0 doesnt work on expression as we dont have all 0 in the same row for all columns.

Please let me know more about it - how to get rid of all these situations ?

Re: supress Null for this year or pre year in the straight table

JonnyPoole — Thu, 26 Nov 2015 15:43:19 GMT

If the years don't exist in the data at all then thats differnent maybe:

//check for no prior year

if( substringcount( concat( {1} Year,',') , $(vPriorYear) ) =0 , null(), <expression> )

//check for current year

if( substringcount( concat( {1} Year,',') , $(vCurrentYear) ) =0 , -1, <expression> )

see if u can post a sample qvf if this doesn't help

Re: supress Null for this year or pre year in the straight table

kishorj1982 — Thu, 26 Nov 2015 23:47:12 GMT

Thanks Jonathan. Now, I got the correct data in DB hence problem is fixed. But I appreciate your help. I will keep in mind above expressions you have suggested. Thanks again.