Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
jessica_webb
Creator III
Creator III

Calculated value in set analyis

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:

  

StartYearValue
20090.898432863
20100.905835632
20110.882654745
20120.903658664
20130.930024836
20140.940725491
20150.92200804
20160.896753071
20170.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

24 Replies
tresesco
MVP
MVP

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

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

jessica_webb
Creator III
Creator III
Author

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)


Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
jessica_webb
Creator III
Creator III
Author

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:

 

StartYearValue
20090.898432863
20100.905835632
20110.882654745
20120.903658664
20130.930024836
20140.940725491
20150.92200804
20160.896753071
20170.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)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
jessica_webb
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Hi Kushal,

This is the outcome I get:

  

StartYearValueAvg(Value) - Avg({<StartYear={$(=max(StartYear)-1)}>} Value)
20090.898432863-
20100.905835632-
20110.882654745-
20120.903658664-
20130.930024836-
20140.940725491-
20150.92200804-
20160.8967530710.0000
20170.900086453-

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

Kushal_Chawda

try below


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

effinty2112
Master
Master

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