Creating weekly date buckets in BIME

Follow

This recipe will show you how to display your weekly data in date buckets rather than just showing the individual week number.

Skill level: Medium

Time required: 20 minutes

Ingredients:

  • 1 calculated attribute
  • Data to measure

Create your calculated attribute

To start, you will need to create a standard calculated attribute (see Creating basic calculated attributes).

To create your date buckets calculated attribute

    1. In your query, click the Calculation engine menu icon () on the right toolbar, then select Standard calculated attribute
    2. Name your attribute ‘Weeks’ or ‘Week Bucket’. You can adjust it later if needed.  
    3. Enter the formula below:

      DATE_FORMAT(START_OF_WEEK_MONDAY([DATE(Date)]), "YYYY-MM-dd") + " " + "to" + " " + DATE_FORMAT(END_OF_WEEK_MONDAY([DATE(Date)]), "YYYY-MM-dd") 

      Note: You will need to update [DATE(Date)] to the name of the date attribute you would like to use. For example this could be date, created_date, etc.
    4. Click Save to create your calculated attribute. . 

Display your results

After you created your calculated attribute, you can add it and any other data to your query. When using the calculated attribute, rather than a pre-built week attribute, your results will display like the image below:

weekly_date_buckets_tip_bime.png

Alternatively, if you’re only interested in seeing the start of the week rather than the full duration, you can simply edit the formula and remove + " " + "to" + " " + DATE_FORMAT(END_OF_WEEK_MONDAY([DATE(Date)]), "YYYY-MM-dd").

The complete formula would look like below: 

DATE_FORMAT(START_OF_WEEK_MONDAY([DATE(Date)]), "YYYY-MM-dd")

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk