3 Replies Latest reply: Sep 20, 2011 7:06 PM by Jennifer Hagen

dynamically divide a set value by number of rows displayed by an expanded dimension

Hello,

I am trying to figure out how to write a calculation to evenly estimate a value for a territory budget based off of a total shared district budget. See attached example.

For example:

I have 3 districts, each with a set \$500 budget. Hypothetically, I want to split the total budget up evenly amongst the available number of territories but that will fluctuate in number with time. Therefore, for District A, only 2 territories have a purchase so the total budget of \$500 needs to be divided by 2 to display \$250 each, whereas District B has 3 (\$166.67 each) and District C has 4 (\$125 each). When the territory dimension is collapsed, the district total for all should add up to \$500.

How would this be done??

Thanks

• Re: dynamically divide a set value by number of rows displayed by an expanded dimension

hei

attach is an example of what you asked for

• dynamically divide a set value by number of rows displayed by an expanded dimension

Thank you Liron. I have tried this expression and it only returns a total for some fields and not others. Would you please help me to understand the components that make up the following expression so that I may make amendments to suit my needs?

sum(aggr(BUDGET/ Count(DISTINCT Total <DISTRICT> TERRITORY),TERRITORY,DISTRICT))

• dynamically divide a set value by number of rows displayed by an expanded dimension

Hello QV gurus,

I am still troubled by this issue and would like to see if anyone can help explain how I may arrive at a successful solution to my problem.

I am returning the correct calculated value for some but not all rows. I do not understand how this calculation is functioning. Can anyone help to clarify??

sum(aggr(BUDGET/ Count(DISTINCT Total <DISTRICT> TERRITORY),TERRITORY,DISTRICT))

Thanks!