Filter on first events in period
I have a requirement to filter a fact table by the first event for each user in a period.
e.g with user A B and C
1: user A - 02 jan 2024
2: user B - 04 jan 2024
3: user A - 05 jan 2024
4: user B - 01 feb 2024
5: user C - 02 feb 2024
If I filter on Jan 24, I want to return events 1 and 2 (excluding 3)
If I filter on Feb 24, I want to return events 4 and 5
If I filter on Year 24, I want to return events 1 2 and 5
My initial solution was to create a custom field on the fact table with that definition
Rank: rank() over (partition by "user" order by "event date") and filter on the field = 1
unfortunately, despite displaying the correct Rank, when it comes to filtering, the translation engine applies the filter on the field without applying the Event Date filter, meaning that it would always return event 1, 2 and 5.
Any other idea?
DRay
·1 year agoHello Bruno,
I’m following up to see if the solutiona offered by AssafHanina or Jake_Raz worked for you.
If so, please click the 'Accept as Solution' button on the appropriate post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.
Thank you.
Bruno
OP1 year agoHi David,
It's possible that Assaf's multi-pass solution might work. However we have internally decided that the generated SQL would be too costly to run on a dashboard refresh so we refactored our solution.
Instead of ranking the events to identify the first in the requested period, we have pre-computed the "previous event date" at DB level and changed our filters to check that the "previous date" is before the requested period's "From date".
It is functionally equivalent and cheaper to compute at refresh time.
DRay
·1 year agoThank you for the update!
Jake Raz
·1 year agoMaybe try looking into the solution outlined here? Rather than creating a new column in the table for the Rank (which, as Assif pointed out, would result in static values instead of dynamic ones), you just use the "RANK" function in a value formula. In the link, they use MAX([date]) for the example, but you could switch it out for a MIN instead, or change the rank so it goes in descending order instead of ascending.
Assaf Hanina
·1 year ago · EditedHey Bruno ,
The Rank Function in the Elasticube isn't dynamic as it's fixed value after the Build is completed, Unlike Live Models which the result set is dynamic.
On the other hand, it's good to have it as it might provide the solution for the dashboard as in the dashboard will use the Rank Function as it require Numeric Value.
Note the solution shared is sample and might not work in all cases.
See an Example Dataset to examine the Use Case:
In the Example, Using the Rank Column which created in the Data Model to Apply Dynamic Rank in the Dashboard filter which will be calculated on the fly and group by the User.
Syntax: RANK([Total rank_asc],"ASC",[User])
While filter on 02/25 the rank calculation is Dynamic while in the data model the value is not changed. see example
Than added a widget filter on the Dynamic Rank Column to Set it for 1.
the Output in the dashboard
Additional Example Filtering on 03/2025
Best Regards
Bruno
OP1 year agoThanks Assaf,
Just to clarify, I'm using a live cube. I understand what you mean and I would not expect this to work with an Elasticube. However a live cube will also implement the Rank() in a subquery using the full table (i.e. before the time filtering), this is why my solution failed to retrieve the correct records.
I tried implementing something along the lines of your suggestion, which works to retrieve the correct granular records IF/WHEN "User" is a queried dimension (otherwise it errors out due to a translation limitation, as far as I can see).
Unfortunately my requirement is to run an average ( AVG(score)) over the whole set of events, so I'm back to square 1.
Assaf Hanina
·1 year ago · EditedHey Bruno ,
i understand, the rank of the date is by the user but the actual return should be the Score related to the date and the User present as indicator.
It might be possible combining the Rank function with Multi Pass Aggregation
The Sum function Support a Group By.
DRay in case it's not resolving the issue it require additional review
Best Regards