Create a measure to disaply the max value from a column

Hi

I have a table with the below data and I want to create a measure to display the same values as the custom field. I haven’t been able to do this since the max() function can’t aggregate measures that are already aggregated. anyone know a way to approach this ?

regards
Alex

0 5 7,398
5 REPLIES 5

You could create a Native Derived Table (NDT) that computes the max on a measure but you would be limited to a specific subset of defined dimensions to compute that measure (your NDT computes the max aggregated by day but in your explore you could had more dimensions like campaigns, account, etc. which would then skew your results).

What’s your use case exactly? Maybe we could figure out something else.

I dunno if it’s the most stable/robust solution, but I’ve found some use cases where it seemed justified to put a window function into a measure:

measure: max_of_downloaded_across_dims {
  type: number
  sql: MAX(${client_downloads_measure}) OVER (PARTITION BY NULL) ;;
}

Your final SQL would look like this:

WITH data as ( SELECT 'A' as dim, 3 as value UNION ALL SELECT "B",2 UNION ALL SELECT "C",5 UNION ALL SELECT "B",4)
SELECT
  dim,
  SUM(value) as val,
  MAX(SUM(value)) OVER (PARTITION BY NULL) as max_val
FROM data
GROUP BY 1

Hi Cyril, and thanks for your Reply!

This particular case is quite narrow since it is part of an estimation model im creating. We are using the maximum value in the column as a value that regulates the high-end of the estimation. This estimation would in the next step act as the dataset for our confidence intervals which I have to build out manually in our view. I do not want to build out the CI as a Table Calculation since we may have to use it in several different looks. I would thus need to build out several aggregated measures for averages, variances, standard deviations and so on. Should I create all of these in one ndt?

In the finished product we will need to be able to apply filters for campaign names, market, product etc, to be able to look at subsets, and would that be a problem if I create this via a ndt?

Hi Fabio and thanks for your Reply!

Your solution works, So I will use it if I cant find any other solutions. Want to try to solve it by derived tables aswell as Cyril mentioned above.

Care to explain why this isn’t a stable solution?

Other than showing an unexpected value in you ask for totals, this one should be ok, since the partition by doesn’t reference any fields.

Generally developers can run into issues if they try window functions that partition by a specific column, as it can sometimes be confusing for end users how this “measure” interacts with other fields that may or may not be in their query.

Top Labels in this Space
Top Solution Authors