Hi everyone,
I have a table listing data for API calls with start time and run time.
In a box plot, I can conveniently see min/max/median/percentiles.
In a table, however, I can only add run time once, and then select just average, min or max - so (without calculated) only one metric is possible.
Is there a way to achieve this without duplicating the runtime field into “runtime2” (max), runtime3 (min) etc?
Hi Erik -
There is a way to do by cross joining to a dataset with your measures. The added effort & complexity don’t pay out unless you have a specific use case, like a requirement to filter the measures.
Here’s the solution.
1. Create a measure model for you 5 number summary
2. Cross Join with your dataset.
IMPORTANT: This will duplicate your rows causing a larger/slower dataset that needs to be filtered if used for other things.
3. Create a calculated field to switch between the measures.
ifelse(
element='min',minOver(runtime,[element, truncDate('MM', {start_time})], PRE_AGG),
element='max',maxOver(runtime,[element, truncDate('MM', {start_time})], PRE_AGG),
element='median',percentileContOver(runtime, 50, [element, truncDate('MM', {start_time})], PRE_AGG),
element='q1',percentileContOver(runtime, 25, [element, truncDate('MM', {start_time})], PRE_AGG),
element='q3',percentileContOver(runtime, 75, [element, truncDate('MM', {start_time})], PRE_AGG),
null
)
4. Create a calculated field to sort your custom measures
c_order
ifelse(
element='max',5,
element='q3',4,
element='median',3,
element='q1',2,
element='min',1,
null
)
Result:
