Question

Average per Date

  • 26 February 2019
  • 3 replies
  • 3242 views

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())

Reply