Sum of MaxOver calculated field

Q&A
,
1

I have the below calculated field that is working correctly as for company id 6405 indeed it takes the max and instead of 441,262.5 I see the correct monthly amount that is 10,762.5 but when I take out the company id it goes down to 0. it is aggregated in the values field as min.
The goal here is to have a KPI that will add all the values and it will show the 25,782.5 which is the correct amount and not 456,282.5

image

ifelse(
    {company_id} = 6405 AND (extract('YYYY', calendar) = '2025'), 
    maxOver({subscription_usd_revenue}, [{company_id}], PRE_AGG), 
    sumOver({subscription_usd_revenue}, [{company_id}], PRE_AGG)
)

image
image391×474 9.45 KB

1 Like
2

Hello @pantelis, so the problem you are facing is due to the company_id partition value that you are using in the calculated field. Once you remove company_id from the visual, it no longer has a way to reference the partition, so it will return the max value across all of the data.

In order to achieve the expected value, you will need to filter the visual to the specific company_id. That should resolve the issue you are facing!

1 Like
3

Are you intending to return the sum from this calculation when done at company_id level?

I think you may need to do a sumOver(calculatedfield,[company_id])/countOver(company_id.[company_id]) to fetch this.

Explanation: when you have the company_id added to table, the calculation is done at each ID level and for total, aggregation used is sum.

When you remove company_id field from the table, the calculation is done at id level, but based on your selected aggregation (sum/min) it is aggregated for all counts of company_id present in data.

So we force the calculation to sum for all company_id and then divide by no. of time the company_id is present. Now, you can set the aggregation to sum.

1 Like
4

Hi @prantika_sinha ,

I did the below but I get an error.

image

image
image908×651 20.4 KB

5

Hi @DylanM

sorry but I didn’t understand your suggestion.
What you mean to filter the visual to the specific company_id?

I need a KPI to sum the revenue for all companies. To explain further my data is like below. there is a column for subscription usd revenue and for all companies varies based on some conditions (example company 6186) but for company 6405 is fixed at 10762.5 for every month.

image
image698×515 31.9 KB

so in my calculated field I have the below trying to get one value for company id 6405 and the sum for each of the other companies. And in the visual it is aggregated as min

ifelse(
    {company_id} = 6405 AND (extract('YYYY', calendar) = '2025'), 
    maxOver({subscription_usd_revenue}, [{company_id}], PRE_AGG), 
    sumOver({subscription_usd_revenue}, [{company_id}], PRE_AGG)
)

image

Now I want to create a line diagram summing the revenue for each month but I get double counts for all other companies except 6405

image
image395×410 19.1 KB

For just one month it is counted correctly but for many months it is doublecounted

6

Hi,

I found a solution with the below calculation. Thanks for your help. They pointed to the right direction as indeed it was not partitioning well.

min(minOver(ifelse(
{company_id} = 6405 AND (extract(‘YYYY’, calendar) = ‘2025’),
maxOver({subscription_usd_revenue}, [{company_id}], PRE_AGG),
sumOver({subscription_usd_revenue}, [{company_id}, extract(‘MM’, calendar)], PRE_AGG)
), [{company_id}, extract(‘MM’, calendar)], PRE_AGG),[{company_id}, extract(‘MM’, calendar)])