Calculate Field - Division - Mismatched Aggregation

Question & Answer
,
1

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 Row Rank is another calculated field which will just rank my data records as there are {CaseID} level duplicated within the data which all have {totalfee} and when it sums it I only want it to sum once per {CaseID} and that was the only way I knew how to do it.
The {Total Payment Received Onwards} is a sum of 4 other calculated fields which all look like this with different states: : distinct_countIf(caseid, state = ‘State 1’ OR state = ‘State 2’)
So I essentially have 4 pairs of matching calculated field where one will distinct count the {CaseIDs} and the other will sum the {totalfee}.

I am getting an error of Mismatched aggregation: ‘Custom aggregations can’t contain both aggregate “COUNT” and non-aggregated fields “COUNT(DISTINCT CASE WHEN…’

This is an example of how my data looks and why I created the {CaseID Row Rank}:

Quicksight Screenshot 20230303
Quicksight Screenshot 20230303547×516 43.9 KB

I am not sure about how to get this average field into my data/visual. I can provide further screenshots if that would be of any use.

Any help will be greatly appreciated, thank you.

Riley

2

What calculation is this failing on? the Total Payment Received Onwards?

Or when you divide the two?

3

Hi Max,

Thank you for your reply.

It is failing on the division part {Total Fee Value}/{Total Payment Received Onwards} with the error ‘Mismatched aggregation. Custom aggregations can’t contain both aggregate “COUNT” and non-aggregated fields “COUNT(DISTINCT CASE WHEN…’

Let me know if you need more information.

Thank you.

Riley

4

Total Fee Value is not aggregated while Total Payment Received Onwards is an aggregation.

You will need to aggregate Total Fee Value.

you can use sum({Total Fee Value})/{Total Payment Received Onwards}

5

Hi Max,

Ah, something so simple.

Thank you very much.

Riley