Problem with IF statement

[toni_pienimaki]

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?

[DavidŠtorek]

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

[timpoismans]

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]

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]

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]

Example with history flag

[timpoismans]

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]

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]

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.

[timpoismans · Accepted solution]

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)