Forum Discussion

JeromeMitchell's avatar
JeromeMitchell
Qrew Member
2 years ago

Jinja2 Expressions for date quarter

For my pipeline file output name I'm trying to format an output report filename field based on a specific date in Jinja2. Based on that date the naming convention should include which quarter the data contains.  

ex. Report Date = 12/31/22

Desired outputfile name =  Report_(#QYY)_<MM-DD-YYYY> eg. Report_Q422_01-10-2022

How can I manipulate the date to return a number for the quarter ?



------------------------------
Jerome Mitchell
------------------------------
  • DougHenning1's avatar
    DougHenning1
    Community Manager
    2 years ago
    This Jinja should format the report title (replace reportDate with your field name):
    NOTE: Edited to fix quarter formula
    {% set reportDate = time.now - time.delta(months=1) -%}
{% set year = reportDate.strftime('%y') -%}
{% set qtr = (reportDate.month/3) | round(method='ceil') | int %}
{{ "Report_Q%s%s_%s" | format(qtr, year, reportDate|date_mdy) }}​

    ------------------------------
    Doug Henning
    ------------------------------
    • PrashantMaheshw's avatar
      PrashantMaheshw
      Qrew Captain
      2 years ago
      absolutely amazing Doug ! On my basic testing on live parser

      it's not able to parse 
       reportDate|date_mdy

      Below seem to work. What could I be doing wrong @Doug Henning

      {% set year = reportDate.strftime('%y') -%}
{% set qtr = (reportDate.month / 3) | int -%}
{{ "Report_Q%s%s_%s" | format(qtr, year, reportDate.strftime('%Y-%m-%d')) }}​​


      ------------------------------
      Prashant Maheshwari
      ------------------------------
      • DougHenning1's avatar
        DougHenning1
        Community Manager
        2 years ago
        Hi @Prashant Maheshwari, glad it was helpful!  The filter `date_mdy` is a custom filter by Quickbase so it won't work in other environments like live parsers.  Nice job of figuring out a workaround- you're becoming a Jinja master!
        ​​

        ------------------------------
        Doug Henning
        ------------------------------
    • JeromeMitchell's avatar
      JeromeMitchell
      Qrew Member
      2 years ago
      I don't have an actual field name called reportDate.  So I just did everything in the report filename field.  I was using {{(time.now - time.delta(months=1)).strftime('%m%d%y') as the value of the "reportDate" field in my original example. 

      The only real problem I have is for the quarter value, it is coming back as 4.0 instead of just 4, how can I get rid of the decimal point ?
      Thanks for you assistance.

      ------------------------------
      Jerome Mitchell
      ------------------------------
      • DougHenning1's avatar
        DougHenning1
        Community Manager
        2 years ago
        You had the ')' before '.month' instead of after it, so the 'int' filter wasn't getting applied to the final value.  Also, the formula I posted didn't always produce the correct result.  Here's the corrected version with your dates cleaned up:

        {% set reportDate = time.now - time.delta(months=1) -%}
{% set year = reportDate.strftime('%y') -%}
{% set qtr = (((reportDate.month - 1) / 3) + 1) | int -%}
{{ "Report_Q%s%s_%s" | format(qtr, year, reportDate|date_mdy) }}​​


        ------------------------------
        Doug Henning
        ------------------------------