sum year-1 with multiple years selected

[#1]

Dear all,

I am trying calculate year-1 figures in the PT below:

In my calendar I have selected years 2007, 2008 and 2009.

"Visits" is simply sum(Visits) and now I want to display Year-1 figures in the same row.

So, for example, in 2009 Q1 Year-1 I want to display 21,558 i.e. the corresponding 2008 value. I can then calculate a variance accordingly.

Can anyone offer a solution?

Many thanks.

[deepakk]

try out the below two expression.

Sum(if(Year=(Year-1),Visits))

or

Sum( {$<Year = (Year-1)>} Visit)

[#3]

Thanks for the suggestions. The expression does not like the 1 so just returns selected year visits not year-1 visits.

I don't think this will work if I select more than one year either.

Perhaps I need to add a Year & Quarter -1 to my calendar.........

[deepakk]

Currenlty I dont have QV installl in my system hence I cant try...

but try using the P function .... P(Year)-1

[johnw]

Set analysis won't solve it because a set is only calculated once for the entire chart, not once per row (year) in the chart.

I don't think you can do it with an if() either, since a condition like Year=Year-1 will always be false. There's no easy way in QlikView to say "if the year for the row of data in the data model is equal to one less than the year of the row in my chart". There might be some way to pull of an interesting expression that would be the equivalent of that, but I'm not thinking how, and it's not how I'd solve the problem anyway.

I generally recommend generating an AsOf table for this sort of case.

AsOfYear, YearType, Year
2009, Current, 2009
2009, Previous, 2008
2008, Current, 2008
2008, Previous, 2007
etc.

Then use AsOfYear instead of Year in the chart, and use YearType as a dimension instead of having a separate expression for year-1.

[deepakk]

hi

Jhon is right it will not work with if and set analysis.

But in Year-1 expression try out

above(Visits)

where Visits is the first expression name..!!

[#7]

Hi John,

I'm not entirely sure how to model this. I've tried using YearType = {'Previous'} in the set but I don't think that's what you are suggesting.

If possible, do you have a sample app that uses this concept that you could post?

Many thanks.

p.s. Deepak, I've tried using the above function which works ok in this simple example but I would like to deploy this solution to other more complex objects where looking at the value directly above will not work. Thanks anyway.

[johnw]

Attached is an example. What I was recommending was the pivot table approach on top, but that doesn't let you calculate a difference. The bottom chart shows how you can combine set analysis with the AsOf table to calculate a difference if that's important.