Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Master II
Master II

Try below

sum({$<Year*={">=$(=year(today())-3)"}Sales)
Learning never stops.
Highlighted
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)

Highlighted
Partner
Partner

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.
Highlighted
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

Highlighted
Partner
Partner

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