7 Replies Latest reply: Oct 27, 2010 2:10 PM by Karl Pover

# Need to divide by an aggregation

Is it possible to divide by an aggregation calculation either in the expression or calculated dimension field?

• ###### Need to divide by an aggregation

You can divide within the aggr function like AGGR(Sum(Renewed)/sum([ Quantity]), [ Account Name]), but are you looking to do something like AGGR(Sum(Renewed),[Account Name])/AGGR(sum([ Quantity]), [ Customer])?

Do you have an example of what you want to do?

Regards.

• ###### Need to divide by an aggregation

Correct, I am trying to divide two separate aggregations. See example below:

aggr(sum([Line Count]),Procedure,State,Specialty)/aggr(sum([Line Count]), Specialty, State)

The reason for the separate aggregations is that the numerator is being aggregated over three dimensions while the denominator is being aggregated over two dimensions.

• ###### Need to divide by an aggregation

If you divide the aggr functions like that, it will return a number that appears to be one element of the list generated by aggr in the numerator by the one element of the list generated by the aggr in the denominator.

I don't think that is what you want. Can you explain the table or chart you are looking to generate to see if there is another way to formulate it?

Regards.

• ###### Need to divide by an aggregation

I have three dimensions: procedure code, state, and specialty. I want to take the number of line items where that procedure code exists for that state and specialty and divide it by total number of line items for that state and specialty to get a usage percentage within the state and specialty. Hope I explained that well enough, can be kind of confusing.

Thanks for your help on this!

• ###### Need to divide by an aggregation

If your chart has those 3 dimensions, you should be able to use the expression

sum([Line Count]) / sum(Total <Speciality, State> [Line Count])

to get the percentage that you are looking for.

Regards.

• ###### Need to divide by an aggregation

That won't work because it will give me everything for all specialties and states in the selection and I need it for the respective state and specialty belonging to a practice.

• ###### Need to divide by an aggregation

The modifiers should restrict the total by specialty and state if your chart has those fields as dimension. Can you put a small example of the table you are looking for?

Regards.