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?
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