topic Sum and average of derived expression? in QlikView

Sum and average of derived expression?

Wed, 03 Oct 2012 19:58:14 GMT

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

Re: Sum and average of derived expression?

swuehl — Thu, 04 Oct 2012 09:43:27 GMT

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

Re: Sum and average of derived expression?

Thu, 04 Oct 2012 10:53:29 GMT

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.