topic Differentiate Null Values from 0s in QlikView

Differentiate Null Values from 0s

Thu, 21 Jan 2016 18:13:40 GMT

I have a data warehouse with a million of rows and I want to calculate the fact Sales_Online for different dates. My Fact Table already have records but the however the metric Sales_Online still has the records to 0 € (not null!!!).

I try to differentiate the Null Values from 0s because when I don’t have data I want to show a message error. For that I’m using the following expression in a Object Text:

=IF(isNull(SALES_ONLINE),’NO_DATA’,num(SUM(SALES_ONLINE)/1000,'#,##')&'K')

But, this expression returns the message error even if I selected a day that I’ve records with 0 €.

Thanks!!!

Re: Differentiate Null Values from 0s

sunny_talwar — Thu, 21 Jan 2016 18:16:13 GMT

What about this?

If(Len(Trim(Avg(SALES_ONLINE))) = 0, 'NO_DATA', Sum(SALES_ONLINE)/1000)

Re: Differentiate Null Values from 0s

Thu, 21 Jan 2016 18:27:41 GMT

Hi Sunny T,

Thanks for your response But I think it doens't returns my goal

I' give you a example to represent my problem.

Look at this image:

As you can see all of my Sales_Online is equal to 0. The logic is:

If a I select a date that exists in Fact Table the result is 0 because the Sum(Sales_Online) = 0;
But I select another date the result should be -1 (or Null) because I'm adding missing values.

Are you undestanding my trouble?

Thanks for you help