yellr
May 31, 2023, 4:07pm
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
yellr
May 31, 2023, 6:41pm
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
First create a calculated field for the year like this:
year = truncate(‘YYYY’, {budget_period})
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
yellr
June 1, 2023, 5:20pm
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
4 Likes
Way to go @David_Wong ! Thanks for letting us know this worked for you @yellr
Thank you so much @David_Wong
1 Like