Sum and average of derived expression?

[#1]

Hi,

I would like to get sum, mean, standard deviation and count from a derived expression. I tried many different ways, still cannot figure it out by myslef. Please refer to the sample project in the attachment. Any helps will be highly appreciated!!!

Longmatch

[swuehl · Accepted solution]

I believe you can get your results using advanced aggregation, using expressions in your text box like:

= 'Total  moving range is: ' & sum(aggr(fabs(count(ID)-above(count(ID))),Month))

and

= 'Average of moving range is: ' & avg(aggr(fabs(count(ID)-above(count(ID))),Month))

You will run into one issue here: aggr() function sorts its dimension values by load order, and load order for Month is mixed up, not in chronological order, for your input data. You can see this if you create a list box for Month and set sort order to load order.

You can correct this is by reading in your Month sorted, if this is not possible, you can create all field values of Month ordered correctly as first load in the script, creating the correct load order. After loading in your data, you can drop the field (only from the first table), if you want.

See attached for my solution.

Regards,

Stefan

[#3]

This is what I need, it just works perfectly. Thank you very much. I only need average, standard deviation and sum, so the order is not issue.