Mismatched aggregation errors in QuickSight calculations

Question & Answer
,
1

The topics of “Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination error” are many but I found nothing that could match my use case

I have a dataset (not be able to edit in query level). This dataset contains the transaction logs of employees. Each transaction logs contains:
login_id, event_date_time, event_type, login_position
I want to have a table that showing the logs with the latest position (the position of the latest event_date_time).

The idea is create a calculated field to get the latest position for each login_id, something like this:
ifelse(
event_date_time = maxOver(
event_date_time,
[login_id]
),
login_position,
null
)
However, there is error showed up saying “Mismatched aggregation. Custom aggregations can’t contain both aggregated and non aggregated fields, in any combination”

Any help, assistance is appreciated!

2

Hi @baduong

You can refer to this article and specifically look at " Handling mismatched aggregation errors" section to understand why the error is occuring

If you follow the explanation you probably can make the required changes and fix the issue. If the issue persists then please share a sample dataset and I can try create a sample for you.

Regards,
Giri

1 Like
3

Thanks for your feedback.

By reading the article you shared, I could do it by myself.
Note here for someone who may need:

  1. Create calculation field at LAC-W [cf_Latest_Position_LAC_W]:
    ifelse(
    event_date_time = maxOver(
    event_date_time,
    [login_id]
    ),
    login_position,
    PRE_AGG
    )

  2. Create calculation field at LAC-A [cf_Latest_Position_LAC_A]:

firstValue(
{cf_Latest_Position_LAC_W},
[{login_id} ASC,{cf_Latest_Position_LAC_W} DESC],
[{login_id}]
)

  1. Add cf_Latest_Position_LAC_A to the value section of the table
2 Likes