Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

Differentiate Null Values from 0s

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!!!

2 Replies
Highlighted

Re: Differentiate Null Values from 0s

What about this?

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

Highlighted
Not applicable

Re: Differentiate Null Values from 0s

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:

Capture.PNG

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