Need to divide by an aggregation - Qlik Community

Report Inappropriate Content · ‎2010-10-22

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

pover · ‎2010-10-22

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.

Report Inappropriate Content · ‎2010-10-22

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.

pover · ‎2010-10-22

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.

Report Inappropriate Content · ‎2010-10-22

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!

pover · ‎2010-10-26

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.

Report Inappropriate Content · ‎2010-10-27

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.

pover · ‎2010-10-27

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.