Filter on first events in period

[Bruno]

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?

[AssafHanina]

Hey @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:

1. 3 Users: A,B,C
2. Date Column For Each 
3. Rank Function - Created as Custom Column in the Data Model
4. Date Filter on Month Granularity 
5. Additional Calculated Column on the Dashboard Using Rank Function. 

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]

Thanks 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.

[AssafHanina]

Hey @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

[Jake_Raz]

Maybe 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.

[DRay]

Hello @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 · Accepted solution]

Hi 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]

Thank you for the update!