topic Re: Before function in Qlik Sense Saas and null values in App Development

Before function in Qlik Sense Saas and null values

mike_spada — Wed, 02 Jul 2025 13:20:58 GMT

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.

Re: Before function in Qlik Sense Saas and null values

seanbruton — Wed, 02 Jul 2025 13:36:59 GMT

Hi,

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

Regards

Re: Before function in Qlik Sense Saas and null values

mike_spada — Wed, 02 Jul 2025 17:59:08 GMT

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

Re: Before function in Qlik Sense Saas and null values

Kearawill — Tue, 08 Jul 2025 09:14:47 GMT

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!

Re: Before function in Qlik Sense Saas and null values

jackandero — Thu, 27 Nov 2025 08:48:36 GMT

The Before() function in Qlik Sense SaaS treats null values differently than QlikView. When yearly data is missing, Before(Sum(Amount)) returns null, making your difference calculation fail.

Fix: Replace your expression with:

qlik
RangeSum(Sum(Amount), -Alt(Before(Sum(Amount)), 0))
This converts null values to zero before calculation, metal buildings wisconsin ensuring proper year-over-year comparisons even with missing data.

For detailed expression guidance, refer to Qlik's official documentation. Let me know if you need help implementing this in your pivot table!