Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Well, a correction is needed, double quotes. Try:
AVG({<Year={"$(=(LEFT(Year,4)-1) & '-' & (RIGHT(Year,2)-1))"}>} Value)
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!
I assume, your expression has Typo error. May be this?
avg({<StartYear={'$(=max(StartYear))'}>}Value)-avg({<StartYear={'$(=max(StartYear)-1)"}>}Value)
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?
Second part should be this?
Avg({< StartYear = {'$(=Max(StartYear-1))'} >} Value)
OR
Avg({< StartYear = {'$(=AddYears(Max(StartYear),-1))'} >} Value)
I don't just want it for Max(StartYear) though.
if you already have start year ,then you can use below
Avg(Value) - Avg({<StartYear={$(=max(StartYear)-1)}>} Value)
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!
try below
Avg(Value) - above(total Avg(Value))
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