How to create Date variable for MTD, YTD, QTD..etc
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
Answers
-
Alexandru Dataiker, Dataiku DSS Core Designer, Dataiku DSS ML Practitioner, Dataiku DSS Adv Designer, Registered Posts: 1,212 Dataiker
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. -
@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!
-
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.