Previous Years data by selecting a Year

[mohan2391]

hi

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

[#2]

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.

[#3]

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.

[richard_chilvers]

This may help ? :

Dates in Set Analysis‌

‌

[settu_periasamy · Accepted solution]

Hi Mohan,

Try something Like..

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

[Anonymous]

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

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

[mohan2391]

Ya, Thank you everyone.

Mohan

[settu_periasamy]

Hi Mohan,

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

[mohan2391]

Hi

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.

[#10]

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