different calculations by fiscal year

[ali_hijazi]

Hello
I got a pivot table with Fiscal Year as dimension
what i want to acocmplish is the followoing:
for fiscal years before current fiscal year should display the sum({<scenario={actual}>}amount)
while for current fiscal year and future ones I need to display sum({<scenario={budget}>}amount)

I wrote the following expression:
sum({<scenario={actual},fiscal_year={"<2026"}>}amount)
+

sum({<scenario={budget},fiscal_year={">=2026"}>}amount)

this returns the correct results
However if the user selects the fiscal year 2026, he is still able to see the results for 2025,2024,...
what would the set analysis be so that the user only sees the result of the selected fiscal year
or if he selects 2026 and 2025 he only sees 20206, and 2025 exclusively and not 2024 and others because these are <2026

[lennart_mo]

Hi @ali_hijazi, 

to achieve your desired outcome you need to adjust your expression as follows:

sum({<scenario={actual},fiscal_year*={"<2026"}>}amount)
+
sum({<scenario={budget},fiscal_year*={">=2026"}>}amount)

The '*' in front of the '=' tells the set analysis to combine the current selection with the filter.

You might wanna take a look at the Qlik documentation on this:

Set modifiers with implicit set operators | Qlik Sense on Windows Help

Additionally I would suggest using RangeSum() instead of + for better NULL-Value handling.

Best regards

Lennart

[Chanty4u]

Hi

Try this 

If(

    [Fiscal Year] < 2026,

    Sum({<scenario={'actual'}>} amount),

    Sum({<scenario={'budget'}>} amount)

)

May be this if 2026 is dynamic

If(

    [Fiscal Year] < Year(Today()),

    Sum({<scenario={'actual'}>} amount),

    Sum({<scenario={'budget'}>} amount)

)