How to sum value if Year(Date) >= Max Year(Date) -1 in Set Analysis

[Xytras]

Hi all,

I just started working with Qlik Sense (switching from Spotfire to Qlik).

I'm trying to sum my bar chart data only when the year value from field "MyDate" is >= then Max(Year(Mydate)) - 1 (minus 1).

I tried something like:

Sum( {$<[MyDate.autoCalendar.Year] >= (Max(TOTAL [MyDate.autoCalendar.Year]) - 1)>} ValueInGroupCurrencyUSDFormatted )

but due to my poor knowledge, I've not idea on how to fix it.

Could you kindly help on that?

Regards,

Xy

[ajaykakkar93]

Hi,
Instead of this [ Max(Year(Mydate))-1  ] you can use addyears( Year(Max(Mydate))  , -1)

[Xytras]

Hi Ajay,

Thanks for your reply.

I modified my expression in set analysis as follow:

sum({<[Date.autocalendar.Year]={"$(=addyears(max(Year(Date))), -1)"}>}ValueInGroupCurrencyUSDFormatted)

but it is doing the sum considering ALL the records, it seems it is not considering  this part {"$(=addyears(max(Year(Date))), -1)"}.

Any thoughts?

Regards.

[ajaykakkar93]

Hi,

Can you create a variable with the below expression
=addyears( Year(Max(Mydate))  , -1)

then add the variable in your final expression
sum({<[Date.autocalendar.Year]={$(VARIABLE_NAME)}>}ValueInGroupCurrencyUSDFormatted)