topic Re: Calculate the Average of a pre defined variable in QlikView

Calculate the Average of a pre defined variable

Mon, 19 Nov 2012 17:10:27 GMT

I have defined a variable which performs a calculation.

I then want a seperate field to calculate the average of the output of the variable,

I tried using Avg($(HISTORY))

but to no avail

Re: Calculate the Average of a pre defined variable

swuehl — Mon, 19 Nov 2012 17:14:39 GMT

Please note that you can't embed an aggregation function like sum() into another like avg() without using advanced aggregation function aggr().

So if you variable expands to something like sum(Value), this will not be a valid expression:

avg(sum(Value))

And if your variable expands to a result, i.e. a number already, there is no need to use avg().

So how have you defined your variable?

Re: Calculate the Average of a pre defined variable

Mon, 19 Nov 2012 17:20:18 GMT

Sorry I am still getting to grips with this.

Is this the correct information you have requested?

Re: Calculate the Average of a pre defined variable

swuehl — Mon, 19 Nov 2012 17:23:21 GMT

I can't see a variable named HISTORY, so I would assume no.

Re: Calculate the Average of a pre defined variable

Mon, 19 Nov 2012 17:29:11 GMT

it is correct sorry I changed DELAYMIN to HISTORY

Re: Calculate the Average of a pre defined variable

Mon, 19 Nov 2012 17:48:23 GMT

Re: Calculate the Average of a pre defined variable

swuehl — Mon, 19 Nov 2012 17:58:52 GMT

Please check

http://www.qlikfix.com/2011/06/08/not-all-variables-are-created-equal/

for the difference in using an equal sign or not using one in a variable definition.

In which context do you want to use your new expression? In a chart expression? So you may want to try with creating a variable with no equal sign

Minute(frac(Timestamp1)-frac(Timestamp2))

(and please check the type of brackets you are using, I would suggest using always round brackets () with functions like frac() )

Re: Calculate the Average of a pre defined variable

Mon, 19 Nov 2012 18:12:18 GMT

Thanks for the link.

I have amended the variable as per your advice.

I want the new expression to use the output result of HISTORY to provide the average. If there is another way I have no idea

Re: Calculate the Average of a pre defined variable

Thu, 22 Nov 2012 16:09:48 GMT

Is anyone able to provide any additional assistance?

Re: Calculate the Average of a pre defined variable

swuehl — Fri, 23 Nov 2012 11:15:42 GMT

It would be much easier to help you if you could post a small sample app. Still too many unknowns for me to understand your issue.

Re: Calculate the Average of a pre defined variable

Thu, 29 Nov 2012 09:14:06 GMT

I have attached an example of the data I am working with.

I am trying to find the average of Delay Mins, which has the variable HISTORY.

and I am then going to try and count the data with delay mins of +15

Re: Calculate the Average of a pre defined variable

Thu, 29 Nov 2012 11:04:45 GMT

hi this has been resolved by Jens Frederik Kristiansen

=interval(avg(aggr((sum(CHKS)-sum(SCH)),[FLT-NO],DATE)),'mm')

Tthanks for everyones assistance

Re: Calculate the Average of a pre defined variable

swuehl — Thu, 29 Nov 2012 11:17:54 GMT

Good to hear that this is finally solved.

If you are using the expression in a table chart (like in your sample file), you probably don't need the advanced aggregation, this should get you the same result (in your table Avg Delay Mins):

=interval(avg(CHKS-SCH),'mm')

Re: Calculate the Average of a pre defined variable

Thu, 29 Nov 2012 11:21:30 GMT

Hi Swuehl,

Thanks for the info I have added an additional column to compare the results and you are correct it populates the same output thank you.