Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
satyapaleti
Contributor II

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
Honored Contributor II

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

Try below

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

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

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)

Partner
Partner

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

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
New Contributor II

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

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

Partner
Partner

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

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