Qlik traduction

[EValleMe]

Hi everyone,

I'm having an issue with understanding how Qlik is interpreting what I state. I need it to give me the a number depending on the year I filter for. If I filter for 2019 only, it should give me 2019 FBP. If I filter for any year but 2019, it should give me Net NU. And if I filter for 2019 and other years, it should give me 2019 FBP + Net NU.

The thing is that FBP changes to JU after 6 months and to NU at the end of the year. So based on our date, I would like FBP for 2019 but NU for other years and with the ability to filter for 2017 and 2018 or 2019 and 2017 or other dates but always going for NU if it passed and FBP if it is 2019.

My code looks like this but when I filter for 2019 and other years, it only shows NU and not the FBP:

if([Fiscal Year]='2019', Sum(
{<
[Snapshot Type]={'FBP'},
Baseline=,
Baseline_LE=,
//AAKT-2772
//[Direct Indirect Flag]={$(=$(vDirectIndirect))},
[Default Type] = {$(=$(vCFType))},
//--
[Report Currency]={'$(=$(vSelectedCurrency))'},
[Fiscal Year]={'2019'},
//[Project SC Filter]={1},
[Is Snap Fiscal Year]={1}
,[SC/Non-SC]={"$(=$(vSCNonSC))"}
>}
[Finance Amount]*$(vCurrencyConversionKPI))

+

Sum(
{<
[Snapshot Type]={'NU'},
[Is Snap Fiscal Year]={1},
Baseline=,
Baseline_LE=,
//AAKT-2772
//[Direct Indirect Flag]={$(=$(vDirectIndirect))},
[Default Type] = {$(=$(vCFType))},
//--
[Report Currency]={'$(=$(vSelectedCurrency))'},
[Fiscal Year]={'2018'},
//[Project SC Filter]={1}
[SC/Non-SC]={"$(=$(vSCNonSC))"}
>}
[Finance Amount]* $(vCurrencyConversionKPI))

+

Sum(
{<
[Snapshot Type]={'NU'},
[Is Snap Fiscal Year]={1},
Baseline=,
Baseline_LE=,
//AAKT-2772
//[Direct Indirect Flag]={$(=$(vDirectIndirect))},
[Default Type] = {$(=$(vCFType))},
//--
[Report Currency]={'$(=$(vSelectedCurrency))'},
[Fiscal Year]={'2017'},
//[Project SC Filter]={1}
[SC/Non-SC]={"$(=$(vSCNonSC))"}
>}
[Finance Amount]* $(vCurrencyConversionKPI)),

($(vNU)))

This basically says that if 2019 is selected then show 2019 FBP + Net NU and if it is false then just show previous NU. I think that it is saying if only 2019 is selected only, then it's true.

Is there another way to portray that?

[jonathandienst]

The If() will only work if only one fiscal year is possible, so the If() will only be true if 2019 is selected.

Try this instead:

Sum(
	{<
		[Snapshot Type] = {'FBP'},
		[Fiscal Year] = {$(=Max([Fiscal Year]))},
		[Default Type] = {"$(=$(vCFType))"},
		[Report Currency] = {"$(=$(vSelectedCurrency))"},
		[SC/Non-SC] = {"$(=$(vSCNonSC))"}
		[Is Snap Fiscal Year] = {1},
		Baseline,
		Baseline_LE,
	>}
	[Finance Amount]*$(vCurrencyConversionKPI)
)
+
Sum(
	{<
		[Snapshot Type] = {'NU'},
		[Fiscal Year] = {<$(=Max([Fiscal Year]))},	
		[Default Type] = {"$(=$(vCFType))"},
		[Report Currency] = {"$(=$(vSelectedCurrency))"},
		[SC/Non-SC]={"$(=$(vSCNonSC))"}	
		[Is Snap Fiscal Year] = {1},
		Baseline,
		Baseline_LE,
	>}
	[Finance Amount]* $(vCurrencyConversionKPI)
)

[EValleMe]

It didn't work but it's because we can filter for future years too (2020, 2021, etc.)

I need it to be able to work with different filters too, for the moments we need to change views instantly.

But thanks!