Mismatched Aggregation Issue While Using a Dimension Field

[simantsah]

Hi there,

I’m facing some issues writing a formula. I’ve created a formula called Discovery Formula to calculate the percentage.

countIf(Id, isNotNull({Discovery_Entry_Date__c}) AND StageName = 'Closed Won')/countIf(Id, 
isNotNull({Discovery_Entry_Date__c}))

The problem I’m encountering is that I have a field named StageName, which is a dimension field containing values from A to E. My goal is to use an IF-ELSE statement to check if the “StageName” is equal to ‘A,’ and if so, use the “Discovery Formula”; otherwise, use the “QSO Formula,” which is the same as the “Discovery Formula” but with a different field.

ifelse({StageName} = 'A', {Discovery Formula}, {QSO Formula})

I’m encountering a mismatched aggregation error. Can someone please help me understand what I am doing wrong here and how this can be corrected?

Thank you for your assistance.

[duncan]

Hello @simantsah !

Have you tried doing something similar to the post below and turning {stagename} into an aggregation?

Calculate Field - Division - Mismatched Aggregation

Hi, I am trying to create a calculated field which is essentially {Total Fee Value}/{Total Payment Received Onwards}. It is just to get an Average of the Total Fee Value per day as my visual is just a table which is grouped by a date and has many custom calculated field values. The {Total Fee Value} field is a sum of 4 other calculated fields which all look like this with different states: ifelse({CaseID Row Rank} = 1 AND (state = ‘State 1’ OR state = ‘State 2’), {totalfee}, 0) The CaseID Ro…

[simantsah]

Hi @duncan,

Considering the field {stagename} is a dimension, I don’t think there is a way to turn it into an aggregation. {stagename} contains these five values A,B,C,D, and E and wherever I’ve a measure, I’m making sure it’s properly aggregated.

[abacon]

Hi @simantsah,

You’re receiving the mismatched aggregation error because the “Discovery Formula” and “QSO Formula” are both aggregated fields, which you are then using with a nonaggregated “StageName” field in the ifelse calculated field. As per this document, using custom aggregations (such as your ifelse calculated field) can’t contain both aggregated and nonaggregated fields.

One solution is to treat the StageName Dimension as a measure, for example a calculated field “StageNameMeasure” could be built as follows:
ifelse(StageName='A',1,StageName='B',2,StageName='C',3,StageName='D',4,StageName='E',5,6)

Then your ifelse calculated field “IfCalc” can also aggregate StageNameMeasure
ifelse(MAX(StageNameMeasure) = 1, {Discovery Formula}, {QSO Formula})

So you could build a visual as follows with ifCalc being used in a custom aggregation as follows:
(note - my dataset is small and simple, so only provides calculated values of 0 and 1)

Let me know if it’s possible to treat your StageName dimension in this way.

Regards,
Andrew

[abacon]

Hi @simantsah,

Just checking whether the solution I provided worked for you, if so, could you mark it as a Solution. If I don’t hear back, I will archive this question in 7 days.

Many Thanks,
Andrew