Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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
Partner

hei

attach is an example of what you asked for

Not applicable

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

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!