Calculated value in set analyis

[jessica_webb]

I have a field called Year which uses the format YYYY-YY (e.g. 2010-11) - this cannot be changed

I also have a chart showing average values with the dimension as year and expression 'AVG(Value)'

What I would like is to create a chart, still with dimension as year, but the expression should be the average value for that year, minus the average value for the previous year:

AVG(Value) - AVG(PreviousValue)

How can I get Qlikview to recognise the previous value?

I can calculate the previous year using the following syntax:

=(LEFT(Year,4)-1) & '-' & (RIGHT(Year,2)-1)

So I want something like:

AVG(Value) - AVG({<Year={'(LEFT(Year,4)-1) & '-' & (RIGHT(Year,2)-1)'}>} Value)

But that doesn't work at all!!

UPDATE

I have added a new field in the script which I'm hopeful will simplify this, call 'StartYear' which is just the YYYY of 'Year'.

My table looks like this:

StartYear	Value
2009	0.898432863
2010	0.905835632
2011	0.882654745
2012	0.903658664
2013	0.930024836
2014	0.940725491
2015	0.92200804
2016	0.896753071
2017	0.900086453

What I want now is an expression which calculated the difference between Value and the value of the previous year.

So:

Avg(Value) - Avg({<StartYear={'StartYear-1'}>} Value)

But that doesn't work...

How do I amend the second part of my expression so that it looks at the previous year?

Message was edited by: Jessica Webb

[jessica_webb]

Thanks Kushal - that's worked

[jessica_webb]

Thanks Andrew - you haven't misunderstood! That's exactly what I was looking for (although Kushal got there just before with a similar answer).

I'm a bit unsure about the 'above' function so just reading up on it to make sure it'll work in all my scenarios (as I'm rarely using straight tables).

Are you able to elaborate on the difference between the two correct answers I got?

Avg(Value) - above(total Avg(Value))

compared to

rangesum(Avg(Value),-Above(Avg(Value)))

[Kushal_Chawda]

No problem

[effinty2112]

Hi Jessica,

Rangesum has the advantage over a regular summation in that it won't fall down if one of the arguments is null.

if B is null then

Rangesum(A,B) = A , but A+B  will be null.

Cheers

Andrew

[jessica_webb]

Thanks Andrew. That's a big help.

I think in my scenario then I would want to go for a regular summation as if there is no data for a previous year, then the value should show as null.

Really useful to know!