topic Trouble calculating the average in QlikView

Trouble calculating the average

Mon, 19 Nov 2012 14:40:23 GMT

Im not sure if the output I want is possible I hope it is so here goes.

I have a field which calculates the difference between two times.

At present the information outputted is only correct if I have only one day selected.

Is it possible to display the average for a months set of data but then the actual sum for a single day

Re: Trouble calculating the average

jfkinspari — Mon, 19 Nov 2012 14:52:41 GMT

I think you are looking for something like this:

avg(aggr(sum([Field_with_Differences]),[Field_Date]))

This will sum differences grouped by Date, and calculate the avg. through all dates

Regards,

Re: Trouble calculating the average

Mon, 19 Nov 2012 17:12:49 GMT

Hi Jens, thanks for your reply, I only have one field - If you are at the top level you are able to view the total for one month, as you drill into the information you get the totals for week day etc, I want these totals to be averaged but then when you drill the the lowest point within the information It should show actual information.

Re: Trouble calculating the average

jfkinspari — Tue, 20 Nov 2012 07:25:41 GMT

Hmm, can't quite follow you.
Perhaps you could draft how you would like the table to look like and the principle of the original data, or upload the qvw

Re: Trouble calculating the average

Tue, 20 Nov 2012 11:08:13 GMT

Hi Jens,

Give me a little time and I will upload the data for you with a clear explanation as to what I am trying to do.

Thanks

Re: Trouble calculating the average

Thu, 29 Nov 2012 09:24:46 GMT

Hi Jens apologies for the delayed response I have attached a sample of the data I am using

Re: Trouble calculating the average

jfkinspari — Thu, 29 Nov 2012 09:40:57 GMT

Hi sandston,

I've added a new expression to the existing table calculating the avg number of minutes the flights are delayed.

And this seems to work

I have assumed that the combination of Flight No and date makes the flight unique.

Re: Trouble calculating the average

Thu, 29 Nov 2012 09:43:52 GMT

Hi Jens, I am unable to reopen the file as I am using the personal edition.

could you explain to me what it is I need to add into my document.

Re: Trouble calculating the average

jfkinspari — Thu, 29 Nov 2012 09:51:06 GMT

Okay, this is the expression I have added:

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

It can be beneficial to ad Date as a dimension, to validate the formula

Re: Trouble calculating the average

Thu, 29 Nov 2012 09:53:52 GMT

Hi Jens,

this seems to have worked a treat could you possible explain what the expression means broken down as I wouldnt have known how to create this myself.

Re: Trouble calculating the average

jfkinspari — Thu, 29 Nov 2012 10:13:32 GMT

The aggr function calculates the

sum(CHKS)-sum(SCH)

per possible combinations of FlightNo and Date (this should be equilliant to the unique departure) The result will be a string of numbers like this

6.2,3,1.2,10,4

You can then use any aggreation function, like sum, avg or stddev, to transform the result into on single value.