Average per Date

done
low_priority
lookml
reply

(Rohini Isarapu) #1

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.


(Nicholas Wong) #2

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! :smiley:


(Rohini Isarapu) #3

Thank You Nichol. It worked !!