Discussions

 View Only
Back to discussions
Expand all | Collapse all

Jinja2 Expressions for date quarter

  • 1.  Jinja2 Expressions for date quarter

    Posted 01-10-2023 15:44
    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
    ------------------------------


  • 2.  RE: Jinja2 Expressions for date quarter

    Posted 01-10-2023 16:40
    Edited by Doug Henning 01-11-2023 14:45
    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
    ------------------------------

    Original Message


  • 3.  RE: Jinja2 Expressions for date quarter

    Posted 01-10-2023 19:45
    Edited by Prashant Maheshwari 01-10-2023 19:59
    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
    ------------------------------

    Original Message


  • 4.  RE: Jinja2 Expressions for date quarter

    Posted 01-10-2023 20:17
    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
    ------------------------------

    Original Message


  • 5.  RE: Jinja2 Expressions for date quarter

    Posted 01-10-2023 23:27
    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
    ------------------------------

    Original Message


  • 6.  RE: Jinja2 Expressions for date quarter

    Posted 01-10-2023 23:52
    Edited by Doug Henning 01-10-2023 23:54
    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
    ------------------------------

    Original Message


  • 7.  RE: Jinja2 Expressions for date quarter

    Posted 01-11-2023 09:23
    Thanks, this worked great. Just had a minor question. in the qtr logic :

    {% set qtr = (((reportDate.month - 1) / 3) + 1) | int -%}


    can you explain the (((report.Date.month -1) /3) +1) part?  why do you minus 1 then divide by 3 and add 1 again. 

    Thanks


    ------------------------------
    Jerome Mitchell
    ------------------------------

    Original Message


  • 8.  RE: Jinja2 Expressions for date quarter

    Posted 01-11-2023 10:21
    Edited by Doug Henning 01-11-2023 11:10
    Glad it worked!  Dividing by 3 is because there are 3 months in a quarter (12/3 = 4). The adding and subtracting is to adjust for the math in the month division (month/3).  Using the date 1/1/2023 for example, if you just did month/3 you'd get 0.33 which rounds to 0.

    I found this solution online that works as well:

    {% set qtr = (reportDate.month/3) | round(method='ceil') | int %}

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

    Original Message


  • 9.  RE: Jinja2 Expressions for date quarter

    Posted 01-11-2023 10:56
    HI Doug, 
    Thanks for the compliment ! Appreciated , glad to know about the custom filters by QuickBase super handy.

     
    I also have a small question . What does %s in below mean ? 

    {{ "Report_Q%s%s_%s" | format(qtr, year, reportDate|date_mdy) }}​​​


    ------------------------------
    Prashant Maheshwari
    ------------------------------

    Original Message


  • 10.  RE: Jinja2 Expressions for date quarter

    Posted 01-11-2023 11:20
    Hi Prashant, the format filter uses printf-style formatting, so the %s means replace with a string. There are three %s representing the three variables passed to format.

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

    Original Message


  • 11.  RE: Jinja2 Expressions for date quarter

    Posted 01-12-2023 16:52
    Thank for the explanation on %s. Always a pleasure to learn something

    ------------------------------
    Prashant Maheshwari
    ------------------------------

    Original Message