Question

Date Range in Custom Field

  • 16 January 2021
  • 2 replies
  • 66 views

I’m wondering if it’s possible to set a date range as a custom dimension? For example, I want to compare two date ranges in an IF statement via custom dimension, which would look something like: 

 

if(${sales_order_date} between “2021-01-01”, “2021-01-05”, “week 1”, if(${sales_order_date} between “2021-01-08”, “2021-01-12”, “week 2”, “neither)) 

 

Any help appreciated, thanks!


2 replies

Userlevel 3

Hi Gannon,

 

Is there a reason why you would not want to use the Sales Order Week dimension? It can be added if it is not available in the explore.

 

Alternatively, it can be added as a custom dimension, indeed, but it is not recommended as the custom dimension will only be available on this particular example, and will need to be re-created every time you need it.

 

It is best to work together with the data team to add this type of dimensions in the data model.

 

Hope this helps!

 

Best,

Hi Maddie, 

 

The reason for not using sales order week is that our order week starts Monday and ends Sunday, but I would like to analyze a W/W that starts Saturday and ends Friday. Pivoting on just order week cuts off a couple days, and I can’t seem to find a way to pivot two custom time ranges. 

 

Perhaps there is a way to use a 14 day range with a 7 column pivot_offset()? 

Reply