Median by Day for multiple records

Looker Q&A Looker
,
1

I am new to Looker and I am trying to show the median value by day. If I have the following data, how would I calculate the median of all values for each day? My goal is to display a bar chart with a single median value for each day.

Date Record Value
10/1/2021 1 5
10/1/2021 2 8
10/1/2021 3 9
10/2/2021 4 10
10/2/2021 5 10
10/2/2021 6 4
10/2/2021 7 5

My goal is to get an output that displays the following for each day:

Goal

Day

Median

10/1/2021

8.0

10/2/2021

7.5

Thank you

2

Create a measure of type:median

measure: median_value {

  type: median

  sql: ${value} ;;

}

and then only put DATE + MEDIAN VALUE IN your graph

3

Thank you. I should have specified that I am trying capture the median from a table calculation I created. The simple table calc is a diff function between start date and stop date for each record. My goal is to calculate the daily median value for these listed records.

I had thought that I could pivot the dates in order capture the median value of each Start-Stop calc at the date level but I am unable to do so.

(Created Table Calc)
Date Record Start Date/Time Stop Date/Time Start-Stop Value
10/1/2021 1 10/1/2021 11:25 10/1/2021 11:55 30
10/1/2021 2 10/1/2021 13:49 10/1/2021 14:17 29
10/1/2021 3 10/1/2021 16:27 10/1/2021 17:14 48
10/1/2021 4 10/1/2021 23:39 10/1/2021 23:49 10
10/2/2021 5 10/2/2021 06:04 10/2/2021 07:59 115
10/2/2021 6 10/2/2021 08:28 10/2/2021 09:11 43
10/2/2021 7 10/2/2021 12:04 10/2/2021 12:52 49
10/2/2021 8 10/2/2021 16:52 10/2/2021 18:24 92
10/2/2021 9 10/2/2021 21:40 10/2/2021 22:08 29

Desired output:

4

You’re trying to get a daily-level results but the granularity of your data is not at a daily-level, that’s why you can’t do it. In normal SQL, you would be able to use Window Functions and even though Date is repeated, it would hold the same value.

This is much easier to achieve in LookML because everything you need is already built. You can create dimension duration_minute and feed this dimension your sql_start and sql_end, then add a measure median_duration.

Then in your explore all you have to do is select date + that measure. Your granularity is on date level, which means you will get one row per date, and all calculations are done in the background

5

Thanks for the quick response and explanation. I currently don’t have access to create in LookML so I will be dependent on others for support.