Sortting on aggregated measure on PostgreSQL
I'm looking at trying to find the latest date for user logins in pyramid, using the username dimension and then creating a max formula for date dimension to find the most recent date. Then I am unable to sort by most recent. Has anyone come across this before and found a way around?
Hi Fergus Ustianowski
I think a max aggregation on a date returns a text, which is probably why the sort doesn't work.
Could you create in your model an integer column representing the date - Year*10000 + Month*100 + day?
With version 2023 you can also do it using custom columns.
Then create a max aggregation on this column, and use it for the sort - you can show the max (date) aggregation to show the date on the grid, and then use the max(integer date) to do the sort.
Hope this helps,
Non numeric values in measures are currently handled as NULL or zero. This allows the handling of strings in Measures, which are in many other products exclusively numeric items, without calculations blowing up. This is why you can apply a sort to a Measure as a string, but nothing happens.
We’re adding handling when comparing two non numeric Measures, so they will all be 0 compared to a number but will then have “tie breaker” between them. This in turn will allow the applied sort to work correctly.
Not sure when this will be included, but keep your eyes on the product announcement forum for news on this.
Hope that helps.