Sign up to take part
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
Registered users can ask their own questions, contribute to discussions, and be part of the Community!
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
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.