topic Re: Previous Years data by selecting a Year in QlikView

Previous Years data by selecting a Year

mohan2391 — Tue, 01 Dec 2015 07:07:00 GMT

I have a field like YEAR. By selecting a year in this field, I want to see the data corresponding to 5 years of data. I mean if I select 2015, I want to get data from 2010 to 2015 &if i select 2010 I need 2005 to 2010 data etc...

So how can i get that ?

What is the set expression for that ?

And where I have to mention it ?

Thank you

Re: Previous Years data by selecting a Year

Tue, 01 Dec 2015 14:06:29 GMT

Hi Mohan,

Can I just check if you need the 5 years as one number (i.e. a total) or as separate entities? If they are needed separately, I would have 5 expressions along the lines of…

=Sum ({$<Year = {$(=max(Year)-1)}>} [CashValueField])

=Sum ({$<Year = {$(=max(Year)-2)}>} [CashValueField])

=Sum ({$<Year = {$(=max(Year)-3)}>} [CashValueField])

=Sum ({$<Year = {$(=max(Year)-4)}>} [CashValueField])

=Sum ({$<Year = {$(=max(Year)-5)}>} [CashValueField])

Hope that this is of use.

Regards,

Jason.

Re: Previous Years data by selecting a Year

Tue, 01 Dec 2015 14:08:13 GMT

And if as one number...

=(Sum ({$<Year = {$(=max(Year)-1)}>} [CashValueField]))+(Sum ({$<Year = {$(=max(Year)-2)}>} [CashValueField]))+(Sum ({$<Year = {$(=max(Year)-3)}>} [CashValueField]))+(Sum ({$<Year = {$(=max(Year)-4)}>} [CashValueField]))+(Sum ({$<Year = {$(=max(Year)-5)}>} [CashValueField]))

Regards,

Jason.

Re: Previous Years data by selecting a Year

richard_chilvers — Tue, 01 Dec 2015 14:15:58 GMT

This may help ? :

Dates in Set Analysis‌

‌

Re: Previous Years data by selecting a Year

settu_periasamy — Tue, 01 Dec 2015 16:42:27 GMT

Hi Mohan,

Try something Like..

=sum({<Year={">=$(=Year(AddYears(MakeDate(Year,1,1),-5)))<=$(=Year)"}>}Sales)

Re: Previous Years data by selecting a Year

Anonymous — Tue, 01 Dec 2015 16:54:08 GMT

if your YEAR field contains only Years (2011,2012,..) you need to use this Expression

=sum({<Year={">=$(=Year-5))<=$(=Year)"}>}Sales)

Re: Previous Years data by selecting a Year

mohan2391 — Wed, 02 Dec 2015 05:56:54 GMT

Ya, Thank you everyone.

Mohan

Re: Previous Years data by selecting a Year

settu_periasamy — Wed, 02 Dec 2015 06:31:03 GMT

Hi Mohan,

You can try with HRLinder Suggestion. It looks good. if your year Field Contains only Year like 2011, 2012 etc..

Re: Previous Years data by selecting a Year

mohan2391 — Wed, 02 Dec 2015 06:46:04 GMT

Yes I have years like 2010,2011 etc only. But by applying Linder's expression, I'm getting total of all the years upto current selected year. I don't know why ? So i tried yours, it is fine for me.

Re: Previous Years data by selecting a Year

Wed, 02 Dec 2015 06:51:48 GMT

Hi Settu,

I posted a similar problem, here you see only the previous year, I wanted to have periods from last year period to selected year period.

I have my data:

load * Inline [

Year, Period, Sales

2012,1,1000

2012,2,2000

2012,3,3000

2012,4,1000

2012,5,1000

2012,6,1000

2012,7,1000

2013,1,1000

2013,2,1000

2013,3,1000

2013,4,1000

2013,5,1000

2013,6,1000

];

I have two listboxes for Year and Period. When user selects 2013 and month 6, I want to display the details from 2012 period 6 to 2013 period 5 in a straight table.

Can you please help. I am unable to attach a sample qvw file

Re: Previous Years data by selecting a Year

mohan2391 — Sat, 05 Dec 2015 06:07:47 GMT

It is worked but a small correction. There is 1 extra closed bracket after 5. That's why i'm getting total sales till current year for every selection.

=sum({<Year={">=$(=Year-5)<=$(=Year)"}>}Sales)

Thank You..