Handling null values in date dimension

[Former Member]

I have created a formula for a measure in my model which looks something like this ;

So when both or one of my dates are null the value should be set to -999. But it turns out no matter how I write the formula ISNULL fails to recognize that the date is null. Any suggestions? Here is what my data looks like in the table component in the story. The last column below shows the value from the formula above which is set to 0 since both date columns (the first two) used in the formula are null. When both date columns are null the measure should show -999.

[Former Member]

here are two images if they don't show with my first post.table.jpg

[VijayetaSharma]

Hi daakya99,

Did you get a chance to check above solution..

Thanks

[Former Member]

Hi Vijay

No, I was specifically looking for a solution to ISNULL() function not being able to detect null value. For now, we are using

this workaround below and it works well. Thanks for you help though.

IF ([DateDimension1].Day >"1/1/1000"....

[Former Member]

Thanks, Saurabh.

I found a workaround to the ISNULL() function. I am checking if the date is greater than "1/1/1000" and that seems to work for me. During the model creation all null values are either dropped or replaced with "#" so what is the purpose of ISNULL() function. I couldn't get it to show me with If date ="#" either.

[saurabh_sonawane]

Hi

Try get table data in variable

var result = table1.getdatasource().getsresultset();

use console.logt to check what values does your date field is having

console.log(result)

after that use that in your formula

Mostly it would be undefined in the date field where it is blank but plz get check onces

Thanks,

Saurabh S.