minOver in ifelse to capture first instances of repeated values

Q&A
, , ,
1

I’m attempting to capture the first iteration of ‘serial_number’ across a date range. The serial numbers can have suffixes increasing their character count, so I created a calculated field at the dataset level that compares the first 9 characters and return the first instance of different transaction types associated with that serial number. My dataset feed goes back to 2008 but I have a filter in place to only return records after 1/1/2023. (transaction_date is of date format, everything else is string)

ifelse({transaction_date}=minOver({transaction_date},[{Serial_Extract_9Alpha}],PRE_FILTER),{transaction_type_name},null)

The field works if there is more than one instance of ‘Serial_Extract_9Alpha’, but it fails when there is only one instance.

Example:

serial_number transaction_date transaction_type calculated_field_in_question
1111 1/1/2023 00:00 Completion null
1122 2/2/2023 00:00 Completion Completion
1122 2/5/2023 00:00 Completion null

Desired:

serial_number transaction_date transaction_type calculated_field_in_question
1111 1/1/2023 00:00 Completion Completion
1122 2/2/2023 00:00 Completion Completion
1122 2/5/2023 00:00 Completion null

Any help on why this is happening and how I can solve for the case of singular instances returning null? Thank you.

Edit: I forgot to add that if I use the following calculated field in a visual then it works:

ifelse({transaction_date}=minOver({transaction_date},[{Serial_Extract_9Alpha}],PRE_AGG),{transaction_type_name},null)

I suspect it has something to do with the calculation level?

2

Hi @tjohnson, thank you for your question. We are reaching out to our experts to get you a reply.

3

Here is more information on how aggregations are calculated.

You’re saying the PRE_AGG works for your use case?

PRE_FILTER might be throwing issues if you have any filters on your dataset. It will calculate everything before a filter is applied.