Trouble calculating the average

[#1]

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

[jfkinspari]

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,

[#3]

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.

[jfkinspari]

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

[#5]

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

[#6]

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

[jfkinspari]

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.

[#8]

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.

[jfkinspari · Accepted solution]

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

[#10]

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.