Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

3 Replies
lironbaram
Partner - Master III
Partner - Master III

hei

attach is an example of what you asked for

Not applicable
Author

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))

Not applicable
Author

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!