How to define a custom PERIOD?

Question & Answer
, , , ,
1

How can I define a custom period?

For functions like periodToDateAvgOverTime, you can use DAY, WEEK, MONTH, etc.

But what if I want a periodToDateAvg for an aggregated metric for the last 6 weeks instead of the last WEEK? How can I define a 6 week PERIOD value?

2

Hey @oconnellpatrickc ,

something like:

periodToDateAvgOverTime(
     sum(
          ifelse(
               date<Date_Parameter 
               AND 
              date > Date_Parameter_2,
                   {fare_amount}, 
                    NULL
               )
      ),
pickupDatetime, YEAR)

should work. You ask for a yearly period but cause you only use certain time frame defined by parameter e.g. 6 months you only use the calculation for that timeframe.
If you have any question or need more help feel free to ask.

BR
Robert

3

Hi @oconnellpatrickc,
It’s been awhile since we last heard from you, did Robert’s solution assist with your original question or do you have any additional requests?

I’ll go ahead and mark his answer as the solution but if you do have any additional questions, feel free to create a new topic.

Thank you!