Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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!