topic Re: Retrieve data for the past 7 year in bar chart using set analysis in QlikView

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

Sun, 26 Mar 2017 05:08:33 GMT

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.

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

bouderbc — Sun, 26 Mar 2017 19:00:59 GMT

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 ])

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

Anonymous — Mon, 27 Mar 2017 01:48:15 GMT

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

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

Anil_Babu_Samineni — Mon, 27 Mar 2017 02:13:25 GMT

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

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

Anil_Babu_Samineni — Mon, 27 Mar 2017 02:14:53 GMT

yddona.lyn

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

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

Anil_Babu_Samineni — Mon, 27 Mar 2017 02:17:13 GMT

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

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

adamdavi3s — Mon, 27 Mar 2017 06:19:34 GMT

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)