Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SatyaPaleti
Creator III
Creator III

Last 3 years in SET analysis Issue and selections also should include

Hi Folks,

I am calculating last  3 years data by using set analysis from current year 

 

sum({$<Year={"$(=Max(Year)-3)"}Sales)

 

for example I am calculating  2016 , 2017, 2018 ,2019 data.  but if i select year 2017 it have to show only 2017 data if i select 2018 it have to show only 2018 but my selections are not effecting. Can some one help me how to resolve it

 

Thanks and Regards,

Satya

5 Replies
pradosh_thakur
Master II
Master II

Try below

sum({$<Year*={">=$(=year(today())-3)"}Sales)
Learning never stops.
volakakis
Contributor II
Contributor II

Just to clarify something, with your set analysis you are just calculating 3 years ago . For example today is 2019 -3 = 2016

This is why in the set expression suggested above you will need to use the > or delimit your year set with something like

>=$(=year(today())-3) & < year(today) in case you have also data for later years (predictive data etc)

rick_vansoest
Partner - Contributor III
Partner - Contributor III

I always prefer solving stuff like this in the script.

So add field to you calendar:
If(Year >= (Year(Today())-2) , 1 , 0) as FlagLast3Years

Change your set analysis to:
Sum({<FlagLast3Years = {1}>}Sales)

And your sales measure will still respond to year selections.
volakakis
Contributor II
Contributor II

But you lose the ability to check the L3Y from a selected date. for example I want to see 2018 -3 Years and not 2019 -3 years

rick_vansoest
Partner - Contributor III
Partner - Contributor III

I agree. But it all depends on the use case.

Satyapaleti said:
'but if i select year 2017 it have to show only 2017 data if i select 2018 it have to show only 2018'

so your solution will still show 2017 when selecting 2018