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?

1 Solution

Accepted Solutions
timpoismans
Specialist
Specialist

It isn't a typical flag, as in self-made flag. 

His flag holds the values 1 or Null() so the second part of your set analysis won't work.

 

 

Easiest way would be to do indeed use Set Analysis, but re-create the flag in the script.

If(IsNull(FLAG_HISTORY),0,1)

And then use Set Analysis in the following way:

Sum({<FLAG_HISTORY={1}>}sales)
+
Sum({<FLAG_HISTORY={0}>}sales)

View solution in original post

11 Replies
DavidŠtorek
Creator III
Creator III

Hi,
what for is FLAG_HISTORY? Could you show your datamodel and provide few more rows of your data table?
timpoismans
Specialist
Specialist

Hi Toni

Try the following:

If(FLAG_HISTORY=1,
sum(Sales),0)
+
if(isnull(FLAG_HISTORY),
sum(Sales),0)

This ensures that your if's never return a null value, which could cause problems in such a measure.

Do let us know if this doesn't solve the issue and we'll help you further.

 

Kind regards,

Tim P.

 

toni_pienimaki
Partner - Contributor II
Partner - Contributor II
Author

Ho Both and thank for your replys so far!

My example was I bit too simple..
In my final solution I would have to calculate profit. Originally in history table there is Profit already as a column. But from sales table I will have to calculate it. And that's what the Flag stands for.

So the formula would be:

If(FLAG_HISTORY = 1, Sum(Profit),0)

+

If (isnull(FLAG_HISTORY), (SUM(sales) - SUM(purchase)),0)

Example end result in attached picture.

timpoismans
Specialist
Specialist

Could you possibly give us a data example that's representative of the data you're using?

The values that we see in your example, are they correct except for the total?

 

How is your total calculated? (In your measure, you can select what kind of total you want to see, default it's on automatic)

toni_pienimaki
Partner - Contributor II
Partner - Contributor II
Author

Example with history flag

timpoismans
Specialist
Specialist

So the values shown are correct, but the total isn't working as intended yet?

Could you check the following:

How is your total calculated? (In your measure, you can select what kind of total you want to see, default it's on automatic)

toni_pienimaki
Partner - Contributor II
Partner - Contributor II
Author

Hi,

Sorry I cannot give exact data example because these are from our customer.

However values in lines are correct. Total is wrong.
But you are correct!
when I change "Totals function" to Sum in table chart the Total value is also OK.

Unfortunate I would have to use the same expression alos in Bar chart and there is no such a possibily to change totals function 😞

eberry
Contributor
Contributor

If you're looking to use the flag to exclude or include sales figures based on whether they are historical or not, I'd recommend using Set Analysis over an IF function.

To replace the IF function try: sum({$<FLAG_HISTORY={1}>} Sales)+ sum({$<FLAG_HISTORY={''}>} Sales)

See attached image for how this looks in practice.

 

image.png

 

 

 

timpoismans
Specialist
Specialist

It isn't a typical flag, as in self-made flag. 

His flag holds the values 1 or Null() so the second part of your set analysis won't work.

 

 

Easiest way would be to do indeed use Set Analysis, but re-create the flag in the script.

If(IsNull(FLAG_HISTORY),0,1)

And then use Set Analysis in the following way:

Sum({<FLAG_HISTORY={1}>}sales)
+
Sum({<FLAG_HISTORY={0}>}sales)