Average per Date

  • 26 February 2019
  • 3 replies

I have two columns: Created Date(Dimension) and Count (Measure).

The created date is filtered by 7 complete days. I want to find the daily average through table calculation if it is possible as I have used derived table to extract the data.

3 replies

Userlevel 4

The 2 columns will produce something like this? - Looker will automatically take distinct date.

Date | Count

D1 | 442

D2 | 463

D3 | 734

D4 | 234

D5 | 234

D6 | 221

D7 | 234

The beauty of Looker is that is allows you to code using SQL which allows you to reuse and modify it however you want to produce your results. Take full use of the LookML instead! Table calculation is really just for quick calculation.

In order to find the daily average, you’ll want something like:

sum of count by date/count of records

measure: average_of_XXX {

view_label: “Item List Fields”

group_label: “Revenue”

label: “Average of XXX”

type: number

value_format_name: usd

sql: ${dollars} / NULLIF(${records},0) ;;


I’m assuming you’re calculating dollars.

After that, just pull up your Looks and add DATE & average_of_XXX and you’re done! 😃

Thank You Nichol. It worked !!

hi! add a alternative resolution using custom dimensions:
In explore:

  1. Select your time dimension[date,minutes,month]
  2. Filter your time dimension.
  3. Select count measure.
  4. Create a custom dimension: ${myview.count:total}/count(row())