0

Sortting on aggregated measure on PostgreSQL

Hey all, 

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?

Thanks, Fergus

3 replies

null
    • Customer Solutions Architect
    • Moshe_Yossef
    • 9 mths ago
    • Reported - view

    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,

      • Ove_Sandau
      • 9 mths ago
      • Reported - view

      Hi Moshe Yossef 

      What I don't understand is why Pyramid isn't able to do this sort natively. If you take the SQL that Pyramid generates and add an ORDER BY clause with the aggregated measure it works a treat. 

      As Pyramid has generated that SQL, why can't it generate the ORDER BY as well? It's impossible to explain to and end user why they can't do a simple sort.

      It's also an issue I think, that Pyramid says it has ordered it, or even that you have the ability to do this sorting that doesn't do anything.

      Regards

      Ove

    • Senior Director of Product
    • Ian_Macdonald
    • 9 mths ago
    • Reported - view

    Hi all,

    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.

    Ian

Login to reply

Content aside

  • Status Answered
  • 9 mths agoLast active
  • 3Replies
  • 37Views
  • 4 Following