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

[kishorj1982]

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.

[JonnyPoole]

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 )

               or 

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

          ) , 

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

)

[kishorj1982]

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 ?

[JonnyPoole]

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

[kishorj1982]

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.