Question

# I want to calculate last n days/month sales based on selected date

• 5 replies
• 50 views

• Member
• 2 replies

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!

### 5 replies

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 @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

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

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.