Question

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

  • 22 January 2023
  • 5 replies
  • 50 views

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
Badge +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
Badge +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
Badge +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