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

[tresesco]

Well, a correction is needed, double quotes. Try:

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

[jessica_webb]

Still not working unfortunately.

Have now added a new field into my script called 'StartYear' which just takes the YYYY.

Hoping I'll get somewhere with that!

[Anil_Babu_Samineni]

I assume, your expression has Typo error. May be this?

avg({<StartYear={'$(=max(StartYear))'}>}Value)-avg({<StartYear={'$(=max(StartYear)-1)"}>}Value)

[jessica_webb]

Hi Anil,

Have changed the double quotes to single quotes, but that only works for one year (assuming this is because of the Max function).

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?

[Anil_Babu_Samineni]

Second part should be this?

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

OR

Avg({< StartYear = {'$(=AddYears(Max(StartYear),-1))'} >} Value)

[jessica_webb]

I don't just want it for Max(StartYear) though.

[Kushal_Chawda]

if you already have start year ,then you can use below

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

[jessica_webb]

Hi Kushal,

This is the outcome I get:

StartYear	Value	Avg(Value) - Avg({<StartYear={$(=max(StartYear)-1)}>} 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	0.0000
2017	0.900086453	-

But I need it to calculate for every year, not just one!

[Kushal_Chawda · Accepted solution]

try below

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

[effinty2112]

Hi Jessica,

This seems to easy so I fear I've misunderstood. Have you tried

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

in your chart?

Regards

Andrew