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

duripav1
duripav1 Dataiku DSS Core Designer, Registered Posts: 3

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

Tagged:

Answers

  • Alexandru
    Alexandru Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,212 Dataiker
    edited July 17

    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.

    Screenshot 2023-09-16 at 11.50.44 AM.png

  • me2
    me2 Registered Posts: 48 ✭✭✭✭✭
    edited July 17

    @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
    me2 Registered Posts: 48 ✭✭✭✭✭
    edited July 17

    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.

Setup Info
    Tags
      Help me…