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

1 Solution

Accepted Solutions
Kushal_Chawda

try below


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

View solution in original post

24 Replies
tresesco
MVP
MVP

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
jessica_webb
Creator III
Creator III
Author

No luck with that either I'm afraid.

jonathandienst
Partner - Champion III
Partner - Champion III

How about:

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

Note double outer quotes for the set expression.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
senpradip007
Specialist III
Specialist III

Try like:

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

jessica_webb
Creator III
Creator III
Author

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

jessica_webb
Creator III
Creator III
Author

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

senpradip007
Specialist III
Specialist III

Could you please share sample qvw with mocked data?

jessica_webb
Creator III
Creator III
Author

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?