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? @Dawid maybe you can help!
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 @Dawid
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
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 @Dawid , nope I haven’t got it working, could you please share a syntax for your approach
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.