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]

Try like:

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

[PrashantSangle]

Hi,

In your script create field for Year

using left(Year,4) as Year

In front end use expression

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

Regards,

Prashant

[jessica_webb]

No luck with that either I'm afraid.

[jonathandienst]

How about:

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

Note double outer quotes for the set expression.

[senpradip007]

Try like:

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

[jessica_webb]

Thanks for the suggestion - it accepts it as an expression (i.e. no errors), but only gives me null values!

[jessica_webb]

Thanks for the suggestion - it accepts it as an expression (i.e. no errors), but only gives me null values!

[senpradip007]

Could you please share sample qvw with mocked data?

[jessica_webb]

Hi Prashant,

I have followed this almost exactly - have kept my field Year as YYYY-YY as I need it elsewhere, but have created a new field called 'StartYear' which just takes the YYYY

I am now using your expression as:

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

But not working. Am I missing something?