How to create Date variable for MTD, YTD, QTD..etc

duripav1

I'm attempting to find a solution for creating Start Date and End Date variables for MTD, YTD, Rolling 12 months, etc., and passing those variables to an SQL dataset to filter the data. Could someone please advise me on how to create date variables with those options?

Thanks,

Venkat

---

Operating system used: Windows

Alexandru

Hi,
If you want to do this project level, then using a scenario with python step should do the trick.

import dataiku
import datetime

def set_date_variables(scope='project'):
    current_date = datetime.date.today()
    mtd_start_date = current_date.replace(day=1)
    mtd_end_date = current_date
    ytd_start_date = current_date.replace(month=1, day=1)
    ytd_end_date = current_date
    quarter = (current_date.month - 1) // 3 + 1
    qtd_start_date = datetime.date(current_date.year, (quarter - 1) * 3 + 1, 1)
    qtd_end_date = current_date

    client = dataiku.api_client()

    if scope == 'project':
        project = client.get_default_project()
        project_vars = project.get_variables()
        project_vars['local']['mtd_start_date'] = str(mtd_start_date)
        project_vars['local']['mtd_end_date'] = str(mtd_end_date)
        project_vars['local']['ytd_start_date'] = str(ytd_start_date)
        project_vars['local']['qtd_start_date'] = str(qtd_start_date)
        project_vars['local']['qtd_end_date'] = str(qtd_end_date)
        project.set_variables(project_vars)
    elif scope == 'global':
        global_variables = client.get_variables()
        global_variables['mtd_start_date'] = str(mtd_start_date)
        global_variables['mtd_end_date'] = str(mtd_end_date)
        global_variables['ytd_start_date'] = str(ytd_start_date)
        global_variables['qtd_start_date'] = str(qtd_start_date)
        global_variables['qtd_end_date'] = str(qtd_end_date)
        client.set_variables(global_variables)
    else:
        raise ValueError("Invalid scope argument. Use 'project' or 'global'.")

# Example usage:
set_date_variables(scope='project')  # To set project-level variables
set_date_variables(scope='global')   # To set global variables

You can add this in the scenario step and run this time-based trigger daily update these variables.

me2

@AlexT
I copied this code to a scenario then using Reporter I outputted a few of these variables through to Teams.

One variable failed to populate, ${current_date} but the rest carried over correctly.

The only issue is the Scenario Outcome says Failed. I am not savvy enough with reading logs to understand why. The only log line I think might be hinting at the root cause:

External libraries file not found 

Thank you for the recommendation!

me2

NVM, I figured it out... I removed these last lines from the code in the scenario step.

# Example usage:
set_date_variables(scope='project')  # To set project-level variables
set_date_variables(scope='global')   # To set global variables

Now the outcome is successful.