Hi all,
I’ve run into what seems like a frustrating limitation with the bubble grouping/aggregation system, and I was wondering if anyone knows a workaround?
Essentially, I’ve been tasked with presenting the most recent occurrences of a survey response value for each customer. In other words, a customer can complete multiple of the same survey, and I need to return the most recent response value for each unique customer/survey pairing.
Response data type looks like:
-Survey: type of Survey
-Customer: type of Customer
-Submission Date: type of Datetime
-Value: type of number
Here’s an example of potential Responses:
-Customer A, Survey A, January 1, 10
-Customer B, Survey A, January 15, 5
-Customer A, Survey B, January 20, 7
-Customer A, Survey A, January 25, 8
And I would need to present:
Survey A, Customer A: 8
Survey A, Customer B: 7,
Survey B, Customer A: 5
Ideally I’d also be able to present other info along with it.
My original thought was to use groupings, grouping by Survey and Customer using “exact”. This works well if I want to get the :count of surveys. However, when I want to present the values, I run into an issue.
Since it’s a grouping, it doesn’t know which of the other fields to present, so I don’t have access to any of the other fields, e.g. Value. I can’t use an aggregation, because the only available operators (min, max, etc…) don’t apply, since I’m not aggregating by the field I want, I’m aggregating by a different field (Submission Date).
What I REALLY want is to group by Customer + Survey, select the Response with the highest Submission date, then return that Response. However, this doesn’t seem to be possible!
Any ideas on how to accomplish this? This seems like a pretty obvious feature to be missing if I’m being honest, so I assume there’s SOME way around it.