topic Re: Calculated value in set analyis in QlikView

Calculated value in set analyis

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculated value in set analyis

tresB — Wed, 02 Aug 2017 09:23:54 GMT

Try like:

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

Re: Calculated value in set analyis

PrashantSangle — Wed, 02 Aug 2017 09:25:55 GMT

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

Re: Calculated value in set analyis

jessica_webb — Wed, 02 Aug 2017 09:27:14 GMT

No luck with that either I'm afraid.

Re: Calculated value in set analyis

jonathandienst — Wed, 02 Aug 2017 09:36:36 GMT

How about:

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

Note double outer quotes for the set expression.

Re: Calculated value in set analyis

senpradip007 — Wed, 02 Aug 2017 09:52:08 GMT

Try like:

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

Re: Calculated value in set analyis

jessica_webb — Wed, 02 Aug 2017 10:04:37 GMT

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

Re: Calculated value in set analyis

jessica_webb — Wed, 02 Aug 2017 10:04:44 GMT

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

Re: Calculated value in set analyis

senpradip007 — Wed, 02 Aug 2017 10:07:21 GMT

Could you please share sample qvw with mocked data?

Re: Calculated value in set analyis

jessica_webb — Wed, 02 Aug 2017 10:25:33 GMT

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?

Re: Calculated value in set analyis

tresB — Wed, 02 Aug 2017 11:04:19 GMT

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

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

Re: Calculated value in set analyis

jessica_webb — Wed, 02 Aug 2017 11:46:00 GMT

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!

Re: Calculated value in set analyis

Anil_Babu_Samineni — Wed, 02 Aug 2017 11:49:13 GMT

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

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

Re: Calculated value in set analyis

jessica_webb — Wed, 02 Aug 2017 11:55:19 GMT

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?

Re: Calculated value in set analyis

Anil_Babu_Samineni — Wed, 02 Aug 2017 12:02:34 GMT

Second part should be this?

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

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

Re: Calculated value in set analyis

jessica_webb — Wed, 02 Aug 2017 12:05:18 GMT

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

Re: Calculated value in set analyis

Kushal_Chawda — Wed, 02 Aug 2017 12:17:20 GMT

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

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

Re: Calculated value in set analyis

jessica_webb — Wed, 02 Aug 2017 12:20:34 GMT

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!

Re: Calculated value in set analyis

Kushal_Chawda — Wed, 02 Aug 2017 12:23:38 GMT

try below

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

Re: Calculated value in set analyis

effinty2112 — Wed, 02 Aug 2017 12:27:49 GMT

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