Retrieve data for the past 7 year in bar chart using set analysis

[#1]

I want to retreive data for the past 7 year i.e if the current is 2017 then I need to get data from 2017 to 2000 in bar chart using set analysis.Can some one please suggest.

[bouderbc]

Hi,

to get data from 2017 to 2000 in bar chart using set analysis:

1-Dimension: Year

2-Measure:

sum({$<Year= {"2011"}+{"2012"}+{"2013"}+{"2015"}+{"2016"}+{"2017"}>} [Sales])

Or  Solution 2:  Sum({$ < Year ={"200*"}> } [Sales ])

[Anonymous]

Hi,

you can create this set analysis:

sum({<Year={'>=$(=max(Year)-7)'}>} Sales)

This will get the maximum year-7.

Output:

Year	sum({<Year={'>=2010'}>} Sales)
2010	500
2011	800
2012	600
2013	800
2014	400
2015	300
2016	200
2017	200

[Anil_Babu_Samineni]

Do you mean 2017 to 2010, assume typo error

May be this?

Sum({$<year = {">=$(=AddYears(Max(year),-7)) <=$(=Year(Today()))"}>}Sales) //This will return last 7 years of sales

[Anil_Babu_Samineni]

yddona.lyn

This will work until year has 2017. What if year field has forecast data

[Anil_Babu_Samineni]

bouderbc

this way also not correct, Because - you are using static data on your set expression for first approach

And Second approach is will return only starting of 200 of year data - 2000, 2001, .... 2009

[adamdavi3s]

This will only work if the user wants to return data based on today's year

You're close but what you actually want to make it completely dynamic is

Sum({$<Year = {">=$(=AddYears(Max(Year),-7)) <=$(=Max(Year))"}>}Sales)