Calculating Averages in a chart

[#1]

Hi all, Posted this earlier but wasnt very clear. I have to calculate an average but remove the last value by date i.e. 1/5/13 to the 1/6/13 so remove the value associated with the 1/6/13 and then calculate the average. This can happen mid month so it is always the latest value by date that is removed.

sum(Amount)/Count of Date less 1

Below is an example

01-May	500	500
02-May	500	500
03-May	200	200
04-May	700	700
05-May	700	700
06-May	700	700
07-May	700	700
08-May	700	700
09-May	700
Actual average	600	587.5

Can someone tell me how I do this with Set analysis

I can get the count working but cant get the sum to work. Does anyone have any ideas?

Thanks

Alan

[#2]

Hello,

See the attach file, I think it can works.

Best regards.

[#3]

Thanks for your help, however the the first column of dates would be all that was in the QV the second column is an example of how i want to calculate the average. i.e I want to sum all but deduct the value with the corresponding latest date and then calculate the average. using a count - 1.

Date	Value
01-May	100
02-May	150
03-May	150
04-May	150
05-May	150
06-May	150
07-May	150
08-May	150
09-May	150
10-May	200
11-May	200
Actual average	154.55
Average I want	150
To calculate   the average after removing the last value (in yellow) via script.

The bold value related to 11-May needs to be ignored for average calculation.

Regards

Alan

[Anonymous · Accepted solution]

hi alan,

is it what you really like to have?

Regards,

[#5]

Thanks very much, not sure how it works but it does. Thanks for your help