3 Replies Latest reply on Feb 6, 2019 7:34 PM by Shinichiro Murakami

# Creating an index against an average?

I thought this was going to be relatively easy. I have a table of orders counts and revenue for various customers. Average Order Value (AOV) = sum(revenue)/sum(ordercount). With the customers on the row shelf, this shows me the AOV per customer, perfect. Now I want to index each of these customers' AOV against the overall average. So if the overall Avg AOV = 100, and Customer 1's AOV = 90, the index would be 90/100 = .9.

So here is my calculated field: (sum(revenue)/sum(ordercount))/{FIXED : sum(revenue)/sum(ordercount)}. But this gives me the error: Cannot mix aggregate and non-aggregate arguments with this function. I thought the LOD calc would just stand on it's own and produce a single value that I could use anywhere. Thoughts?

• ###### 1. Re: Creating an index against an average?

Without seeing data , but   Try this

(sum(revenue)/sum(ordercount))/   min( {FIXED : sum(revenue)/sum(ordercount)} )

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Creating an index against an average?

Well heck, that worked. I did get it working just before this response by creating seperate calculated fields for AOV and the Overall AOV, and then a 3rd calc dividing them. Why though do I have to aggregate the LOD formula to get it to work all in one?

• ###### 3. Re: Creating an index against an average?

Fixed LOD returns the value as row level number (non-aggregated value)

First part of your formula is aggregated expressions and as error message says aggregated and non-aggregated can not be merged in formula.

Then need to fake the non-aggregated formula as aggregated because other way around (Aggregated to non- aggregated) is not possible.

This LOD fixed is just one value and actually aggregation expression does not matter (max , min, avg, probably sum also) but "min" is the easiest to type.

Shin