from the screenshot for amount streamed 2 i want to see last 3 days on row 1 as (126+587+200)
any help with a measure that can help resolve this?
I want to calculate last n days/month sales based on selected date
Userlevel 7
+1
You can do it with a Table Calculation:
sum(offset_list(${amount_streamed}, -2, 3))
this should grab 3 rows, two previous and current (might need to adjust based on your sorting)
Hi
Hi Dawid, well to put this in a different context, what measure will make it possible for me to calculate total amount for last 12 months from the transaction date.
this syntax seem not be working for me
dimension_group: L12M_Start_Date{
type: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: DATE_ADD(${created_date}, INTERVAL -11 MONTH);;
}
dimension_group: L12M_End_Date{
type: time
timeframes: [
raw,
time,
date,
week,
month,
quarter,
year
]
sql: DATE_ADD(${created_date}, INTERVAL 0 MONTH);;
}
measure: Amount_Streamed_L12M{
type: number
sql:SUM (${amount})
(${created_date} > ${L12M_Start_Date_date} AND ${created_date} <= ${L12M_End_Date_date});;
}
thanks
Userlevel 7
+1
Yes, if you needed a total then I understand the need for measures. I usually do something similar to you by using a calendar table and day/week/month offset. Glad you got it working!
Hi
Userlevel 7
+1
If your data is daily, can you not create a second Table Calculation for Last 12 Months? Provided you don’t have gaps in days? Now that I read it again, you won’t be able to do that without Window Functions and those don’t work in LookML.
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.