ContributionsMost RecentNewest TopicsMost LikesSolutionsAPI to access a widget JAQL There is already a prism method to do this (prism.debugging.GetJaql) but we would need a backend equivalent. We would need to fetch the JAQL associated with a table/pivot widget by providing dashboard id, widget id cube name (?) This is to enable us to invoke the /api/datasources/[cube name]/jaql/csv method. Re: Filter on first events in period 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. Re: Filter on first events in period 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. 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? Solved