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