Max Date Filter in Table

[AJ2024]

Hi,

I would like to store the MAX(POINT_IN_TIME) for use elsewhere in the app. I'm working with time series data and often use set analysis like the following to aggregate data for the most recent date only: 

{<POINT_IN_TIME={"$(=max(POINT_IN_TIME))"}>}[# of Members]

I want to do something similar to store the date on its own to hopefully leverage a filter in a table. Right now, the table uses an indicator called EOPD_IND from the data model, which flags if the record is the most recent record in the database. However, I have users that want to filter back to a historical date, say 12/31/2024, and want the record for that data point to show in the table (not the EOPD record). 

I can't simply remove the date filter from the table, either, because the table always needs to be limited to a single POINT_IN_TIME. Any ideas on the best way to achieve this?

Thanks,

[Lech_Miszkiewicz]

Hi, 

If analysis is a point in time analysis most of the time I build a reference calendar with point in time date/period and all required references to it including: max date, relevant quick date periods (YTD,MTD, Full Year), flags for current year, last year, next year etc. Depending on required granularity that reference table can become large. In my cases those are usually monthly periods (think about it as of financial reporting) which makes it much smaller as we only need to have single date per month. 

With such reference calendar I then use "Always one selected value" to select point in time and another always one selected value for quick period: (YTD,MTD, Last Month, QTD, Full Year, All Dates etc...) and within those there is always max date which can be flagged in the script.

Then expressions are very simple as the only thing in set analysis I need to use is offset for the year. 

Below is just an example of how I model the calendar (keep in mind that As At and Quick Date are always one selected)

cheers

Lech

[Vegar]

You could store the MAX(POINT_IN_TIME)  as a variable.

SET max_time_point = MAX(POINT_IN_TIME) ;

Then use that variable instead of the expression it self. 

<POINT_IN_TIME={"$(=$(max_time_point))"}>}[# of Members]

I don't think it brings much value to do so, except that it opens up the possibility to change the definition of max_time_point for all expressions in an efficient way.

IF you are dependent on this set modifier for ALL expressions, meaning that you are always only interested in the  MAX(POINT_IN_TIME) , then you could consider to force the user to only be able to select one POINT_IN_TIME value at the time by enabling the "always one selected value" for that field. By doing so it will be impossible for the users to select more than one value at the time.