SumOver Distinct Values

Question & Answer
, , ,
1

Hello, I’ve been trying to get a calculated field which sum the total_leader_budget. The total_leader_budget is unique per leader and year combination. I’d like to get a calculated field which totals these values. For instance, the new calculated field total_year should be: 15250 for this table. I want to use this new calculated field in KPI and tables. Any suggestion? I’ve been struggling with this for one day. Many thanks!

|leader|budget_period|total_leader_budget_year|
|Leader1|1/1/23 0:00|100|
|Leader1|2/1/23 0:00|100|
|Leader1|3/1/23 0:00|100|
|Leader1|4/1/23 0:00|100|
|Leader1|5/1/23 0:00|100|
|Leader2|||
|Leader3|1/1/23 0:00|250|
|Leader3|2/1/23 0:00|250|
|Leader3|3/1/23 0:00|250|
|Leader3|4/1/23 0:00|250|
|Leader3|5/1/23 0:00|250|
|Leader4|1/1/23 0:00|12000|
|Leader4|2/1/23 0:00|12000|
|Leader4|3/1/23 0:00|12000|
|Leader4|4/1/23 0:00|12000|
|Leader4|5/1/23 0:00|12000|
|Leader5|1/1/23 0:00|1000|
|Leader5|2/1/23 0:00|1000|
|Leader5|3/1/23 0:00|1000|
|Leader5|4/1/23 0:00|1000|
|Leader5|5/1/23 0:00|1000|
|Leader5|1/1/23 0:00|1900|
|Leader6|2/1/23 0:00|1900|
|Leader6|3/1/23 0:00|1900|
|Leader6|4/1/23 0:00|1900|
|Leader6|5/1/23 0:00|1900|

I’d like to get something like this:

leader budget_period total_leader_budget_year total_budget_year
Leader1 1/1/23 0:00 100 15250
Leader1 2/1/23 0:00 100 15250
Leader1 3/1/23 0:00 100 15250
Leader1 4/1/23 0:00 100 15250
Leader1 5/1/23 0:00 100 15250
Leader2
Leader3 1/1/23 0:00 250 15250
Leader3 2/1/23 0:00 250 15250
Leader3 3/1/23 0:00 250 15250
Leader3 4/1/23 0:00 250 15250
Leader3 5/1/23 0:00 250 15250
Leader4 1/1/23 0:00 12000 15250
Leader4 2/1/23 0:00 12000 15250
Leader4 3/1/23 0:00 12000 15250
Leader4 4/1/23 0:00 12000 15250
Leader4 5/1/23 0:00 12000 15250
Leader5 1/1/23 0:00 1000 15250
Leader5 2/1/23 0:00 1000 15250
Leader5 3/1/23 0:00 1000 15250
Leader5 4/1/23 0:00 1000 15250
Leader5 5/1/23 0:00 1000 15250
Leader5 1/1/23 0:00 1900 15250
Leader6 2/1/23 0:00 1900 15250
Leader6 3/1/23 0:00 1900 15250
Leader6 4/1/23 0:00 1900 15250
Leader6 5/1/23 0:00 1900 15250
2

Hello, So far, I’ve been able to get a calculated value which aggregates the values in the table. But when totalling values, only shows the min value, that is 12000. It does not show the total from the values. This is code for calculated value.

ifelse({usage_date}=minOver({usage_date}, , PRE_AGG),{total_leader_budget_year},null)

In addition, when creating a sumOver on top the above calculated field, only works with the table. When using a KPI, only shows the min value. Any idea?
Thanks

total_leader_budget_year total_budget_year SumTest3
12000 7461432
12000 7461432 12000
600000 7461432
600000 7461432 600000
1812000 7461432
1812000 7461432 1812000
1920000 7461432
1920000 7461432 1920000
2457432 7461432
2457432 7461432 2457432
3

First create a calculated field for the year like this:
year = truncate(‘YYYY’, {budget_period})

image
image488×546 6.26 KB

Looking at the data, it’s clear that there are duplicates but you want your sum to be 100 + 250 + 12000 + 1000 + 1900 = 15250.

First you need a way to exclude the duplicates:
min({total_leader_budget_year}, [{leader}, {year}])

This is basically telling Q to take the min of {total_leader_budget_year} for every combination of {leader} and {year}.

Then wrap this min in a sum to get your final result:
sum(min({total_leader_budget_year}, [{leader}, {year}]))

2 Likes
4

Hi David, I really appreciate your help, you saved my day. I created the new calculated value following your calculation and worked. This is also working with the KPI. Many thanks!

sum(min({total_leader_budget_year}, [leader, {budget_year}]))

leader budget_period budget_year total_leader_budget_year Yearly Budget
Leader1 1/1/23 0:00 2023 100 100
Leader1 2/1/23 0:00 2023 100 100
Leader1 3/1/23 0:00 2023 100 100
Leader1 4/1/23 0:00 2023 100 100
Leader1 5/1/23 0:00 2023 100 100
Leader2
Leader3 1/1/23 0:00 2023 250 250
Leader3 2/1/23 0:00 2023 250 250
Leader3 3/1/23 0:00 2023 250 250
Leader3 4/1/23 0:00 2023 250 250
Leader3 5/1/23 0:00 2023 250 250
Leader4 1/1/23 0:00 2023 12000 12000
Leader4 2/1/23 0:00 2023 12000 12000
Leader4 3/1/23 0:00 2023 12000 12000
Leader4 4/1/23 0:00 2023 12000 12000
Leader4 5/1/23 0:00 2023 12000 12000
Leader5 1/1/23 0:00 2023 1000 1000
Leader5 2/1/23 0:00 2023 1000 1000
Leader5 3/1/23 0:00 2023 1000 1000
Leader5 4/1/23 0:00 2023 1000 1000
Leader5 5/1/23 0:00 2023 1000 1000
Totals 13350
3 Likes
5

Way to go @David_Wong! Thanks for letting us know this worked for you @yellr