Stop Relabeling Dates in Your Reports: A Calculated Field to the Rescue!

Have you ever gotten tired of manually labelling dates in your reports as "Current Month," "Last Month," or "X Months Ago"? It can be a tedious task, especially if your reports are updated frequently.

Well, there's good news! This blog post will introduce you to a powerful solution: creating a calculated field. This field will automatically determine the relative position of a date compared to the current date, eliminating the need for manual relabeling.

Here's How to Do It:

1. Access Your Data Source: Go to the "Source" section of your reporting tool and find the option to "Add a Field." Refer to the provided screenshot.

2. Create a Calculated Field: Choose "Add Calculated Field" and paste the following formula into the designated area:

CASE

WHEN EXTRACT(MONTH FROM CURRENT_DATE()) = EXTRACT(MONTH FROM Date ) THEN 'Current Month'

WHEN EXTRACT(MONTH FROM CURRENT_DATE()) - 1 = EXTRACT(MONTH FROM Date) THEN 'Last Month'

ELSE CONCAT(EXTRACT(MONTH FROM CURRENT_DATE()) - EXTRACT(MONTH FROM Date), ' months ago')

END

3. Name and Save Your Field: Give your new field a descriptive name (e.g., "Month Series") and save it according to the instructions in the screenshots.

Understanding the Formula:

This formula uses the CASE statement to analyse the relationship between the current date and the date you're interested in (represented by the field named "Date" in this example).

• Matching Months:  It extracts the month from both the current date and the "Date" field. If they match, the output will be "Current Month."
  
• Last Month: It subtracts 1 from the current month and compares it to the month in the "Date" field. If they match, the output displays "Last Month."
  
• All Other Months: For any other scenario, the formula uses the CONCAT function to join the difference between the current month and the "Date" month (calculated by subtracting) with the text "months ago."
  

Example:

Imagine it's July (month 7) and you're analysing data for May (month 5). The formula would calculate the difference (7 - 5) and display "2 months ago" in the report.

Benefits:

• Automatic Updates: This calculated field dynamically adjusts based on the current date, eliminating the need for manual adjustments.
• Increased Efficiency: Save time and effort by automating date labelling in your reports.
• Consistency: Ensure consistent terminology across your reports.