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

Options
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,209 Dataiker
    Options

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

    import dataikuimport datetimedef set_date_variables(scope='project'):current_date = datetime.date.today()mtd_start_date = current_date.replace(day=1)mtd_end_date = current_dateytd_start_date = current_date.replace(month=1, day=1)ytd_end_date = current_datequarter = (current_date.month - 1) // 3 + 1qtd_start_date = datetime.date(current_date.year, (quarter - 1) * 3 + 1, 1)qtd_end_date = current_dateclient = 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 variablesset_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 ✭✭✭✭✭
    Options

    @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 ✭✭✭✭✭
    Options

    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 variablesset_date_variables(scope='global') # To set global variables

    Now the outcome is successful.

Setup Info
    Tags
      Help me…