Before function in Qlik Sense Saas and null values

[mike_spada]

Hi,

I hope someone can help me with a problem that's driving me crazy.

I migrated an application from QV to Sense Saas, I have a pivot with Customer as dimension, Year on pivot column and Amount. I want simply calculate the difference between years.

I created a table with this expression for Amout: Sum(Amount). In QlikView I had, for the difference, a column under every year with Sum(Amount)-Before(Sum(Amount)). In Sense it works until I have amounts for both years (for ex: 2024 and 2025), but if one of this is null (because there is no value in the entity loaded for that customer - year) it doesn't work and it doesn't calculate the difference. 

Column Year is sorted, Customer's too. 

Anyone has some idea to face this issue?

Thank you in advance.

M.

[seanbruton]

Hi,

Write in the measure that if the value isnull then return 0 using the if and isnull function.

Regards

[mike_spada]

Of course I already tried, it doesn't work.

[Kearawill]

This issue with the Before() function in Qlik Sense SaaS often happens due to how it handles null values differently from QlikView. When one of the years has no data (i.e., is null), Before(Sum(Amount)) returns null too—so the subtraction results in null, not a number.

To fix this, you can use the RangeSum() function with Alt() or If(IsNull(...)) logic to handle nulls explicitly. Try this revised expression:

RangeSum(Sum(Amount), -Alt(Before(Sum(Amount)), 0))

This ensures that if the previous year’s amount is null, it will be treated as 0, allowing the difference to be calculated properly.

For more on handling expressions in SaaS apps, especially useful for industries like facilities management that rely on year-over-year comparisons, check Qlik’s official expression documentation.

Let me know if you'd like help applying this to a full table setup!