Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
toni_pienimaki
Partner - Contributor II
Partner - Contributor II

Problem with IF statement

Hello Qlik gurus,

I have a following problem with my calculation in Qlik sense report.

As an example I have data in "facts" table:

Date, sales, FLAG_HISTORY
27.10.2018, 900, 1 //this from originally from history table
28.10.2018, 1000, - // this is originally from sales table

I have calculation with if statements:

If(FLAG_HISTORY=1,
sum(sales))
+
if(isnull(FLAG_HISTORY),
sum(sales))

In report I have table chart with simension "date" and calculation above as measure. In chart lines everythig looks OK (27.10 sales is 900 and 28.10 sales is 1000) BUT total shows only 900 !??

Same thin is if I replase date dimension with month dimension --> October sales is 900

Am I just misunderstanding something here?

11 Replies
eberry
Contributor
Contributor

Good point - I missed the NULL point as I entered the data manually into my test app which defaults it to an empty string.

They could recreate the flag as you suggest, which is what I would do as they can then use it as a typical binary flag and potentially remove SA altogether and use flag multiplication instead, or they could invert the SA to get the values where flag ISN'T 1, rather than where it is null:

sum({$<FLAG_HISTORY={1}>} Sales)+ sum({$<FLAG_HISTORY=-{1}>} Sales)

toni_pienimaki
Partner - Contributor II
Partner - Contributor II
Author

"is not 1" isn't working unfortunately like =-

I guess this is because of NULL values in My flags.

Anyway seems that I have to modify my script and add something to replace null value in data (for example zero).
Thank you very much for your support!