topic Prior Year Selection in Bar Graph in QlikView

Prior Year Selection in Bar Graph

Tue, 30 Aug 2011 15:56:36 GMT

i've created a bar chart with period along the x-axis showing current year revenue.

i've used set analysis so that the chart shows all period.

i've used an if statement to get prior year period to data into the graph, but its only showing one period at a time.

is there a way of getting prior year data on the same chart as current year data.

thanks

Re: Prior Year Selection in Bar Graph

sbaldwin — Thu, 01 Sep 2011 10:30:25 GMT

Hi, If i understand you correctly you want to be able to select a single year then get that years figures and the one before, in this case as the 2 measures you could use:

=sum({<Year = {$(=only(Year))},Period=>} value)

=sum({<Year = {$(=only(Year)-1)},Period=>} value)

Thanks

Steve

=sum({<Year = {$(=only(Year))},Period=>} value)

Thanks

Prior Year Selection in Bar Graph

Thu, 01 Sep 2011 10:43:41 GMT

Hi Steve,

the issue is to do with an IF statement i have put into the expression..

this is as follows:

i have 3 categories for VersionSelect, they are Budget Forecast and Last_Year.

When i select either Budget or Forecast, the first part of the IF formula works and the graph maps out the data by period for all periods.

when i select Last_Year,this should get the second part of the IF statement to work, but it only shows data for one of the periods and not all the periods even though i've put Period = {"*"} into the set analysis.

i woudl be very grateful for any other suggestions!

many thanks

=if(VersionSelect <> 'Last_Year',

Sum({$<Input_Sheet = {"DM"},

Obj_# = {"*"},

KPI = {"Total print net advertising revenue (£m)"},

KPI_Definition = {"*"},

Owner = {"*"},

BU = {"DM"},

Level = {"*"},

Data_Contact = {"*"},

Data_Source = {"*"},

Period = {"*"},

YTD = {"YTD"},

CurPd = {"*"}>} $(vComp_Version)),

Sum({$<Input_Sheet = {"DM"},

Obj_# = {"*"},

KPI = {"Total print net advertising revenue (£m)"},

KPI_Definition = {"*"},

Owner = {"*"},

BU = {"DM"},

Level = {"*"},

Data_Contact = {"*"},

Data_Source = {"*"},

Year = {$(#=Year-1)},

Period = {"*"},

YTD = {"YTD"},

CurPd = {"*"}>} Actual))

sum({$<Period = {"*"} ,

YTD = {"YTD"},

CurPd = {"*"}>} SumWeeks)

Prior Year Selection in Bar Graph

sbaldwin — Thu, 01 Sep 2011 12:10:27 GMT

Hi, I am not sure i get what you mean, have you got an example qvw?

Thanks

Stece

Re: Prior Year Selection in Bar Graph

Thu, 01 Sep 2011 13:12:54 GMT

Hi Steve,

does this help?

thanks