Compare scalar value to aggregate

I need to be able to identify certain rows in my data set where the value of the row matches a specific aggregate value.  for example, in concept, this is what I would like to write:

if (startdate = max(startdate)) then startdate else null

So, basically it is like a correlated subquery in SQL.  Using a granularity defined by the rest of the items in the search, I need to flag the rows that are the most recent occurrence at that level of granuarity and return that date, otherwise return NULL, after which I can filter out for null values.

We were able to build a workaround for this using an aggregated worksheet by saving an answer that found the most recent occurrence at a specific level of granularity and then joining it to the full worksheet, giving us the ability to filter at that level of granularity.  However, this was a real performance hit and I can't do this for every level of granularity.  This is a very common use case.  What is the best way to approach this?

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • We are looking at ways to create subqueries easily but for now I can think of only two ways to do this.

      - One is the aggregated worksheet that you have already tried.

      - The other is to create a table that will have the max(startdate) at the particular granularity that you want and then join it with the main table. 

  • OK, thanks.  Since the granularity levels will be defined by the user doing the interactive search, unfortunately neither of these options will provide the flexibility needed.  Is this feature on your roadmap, and if so, what is the priority?  I assume that this is a very common use case for many customers. 

    Thanks for the quick response!

  • Yes. It is in our roadmap as it helps a lot in allowing our users to create complex reports easily and thanks for listing your example here as it gives us all the more reason to prioritize it.

Like Follow
  • Status Answered
  • 2 yrs agoLast active
  • 3Replies
  • 593Views
  • 3 Following