How to extract data from most recent quarter

Linda_22165
Linda_22165 Altair Community Member
edited March 14 in Community Q&A

I want to automate extracting most recent quarter information from data that has dates going back to 2018, Ideally I run the full report that contains dates going back to 2017, and then my Monarch workspace extracts the data from the most recent quarter.  Does anyone have any ideas on how to do that?  The formula would need to update to the most recent quarter each time it runs, without manually changing the dates.

I also have Monarch Server(Automator) should there be some functionality for this in there

Tagged:

Answers

  • Baba_Majekodunmi_703
    Baba_Majekodunmi_703
    Altair Employee
    edited March 14
    Hi Linda,

    Is this what you mean as an example?

    Date: 2/15/2018

    Current Quarter: 3/31/2018

    Last Quarter: 12/31/2017

    Something like that?

  • Linda_22165
    Linda_22165 Altair Community Member
    edited March 14

    Yes to extract data that happened in that current quarter

  • Baba_Majekodunmi_703
    Baba_Majekodunmi_703
    Altair Employee
    edited March 14
    Linda_22165 said:

    Yes to extract data that happened in that current quarter

    Well, there are a couple of approaches you could take to this.

    1. One “easy approach” is to create a file in excel that will serve as a look up table. Within it you will have dates from 1/1/2017 and you would flow that down to 2030 or something far enough in the future. And then you would create two additional columns; Current Quarter and Last Quarter. You then just do a look up to that excel file.

    Example

    Date Current Quarter Last Quarter

    1/1/17 3/31/17 12/31/16

    1/2/17 3/31/17 12/31/16

    2. Option two is to use some combination of formulas. I’m not able to test it right but it would I suspect I would use some of the follow formulas;

    LastDay

    Qtr

    Ceiling

    Floor

    I may have time to test it tomorrow if someone doesn’t answer before then.

  • Linda_22165
    Linda_22165 Altair Community Member
    edited March 14

    I think the combination of formulas is the answer I am looking for-please keep me posted

  • Mahmoud
    Mahmoud
    Altair Employee
    edited March 14

    Hi Linda,

    Lets say if you have column with dates for 2017 (M column), and you have column with just one date, e.g. 4/4/2017 (Date column).  In DPS you can create a formula column and use FiscalQrt(M) function.

    You can use the same function for Date Column, FiscalQrt(Date).  Then create a defined filter.

    Attached is the workspace which is done in DPS v2023.1.  Please change file extension from .txt to .dpwx.

    Hope this is what you were looking for.

    Mo

    image

    image

    image

  • Mahmoud
    Mahmoud
    Altair Employee
    edited March 14
    Mahmoud said:

    Hi Linda,

    Lets say if you have column with dates for 2017 (M column), and you have column with just one date, e.g. 4/4/2017 (Date column).  In DPS you can create a formula column and use FiscalQrt(M) function.

    You can use the same function for Date Column, FiscalQrt(Date).  Then create a defined filter.

    Attached is the workspace which is done in DPS v2023.1.  Please change file extension from .txt to .dpwx.

    Hope this is what you were looking for.

    Mo

    image

    image

    image

    By the way you change the fiscal year if in DPS

    image

  • Linda_22165
    Linda_22165 Altair Community Member
    edited March 14

    Testing it out now-thanks so much

  • Mahmoud
    Mahmoud
    Altair Employee
    edited March 14

    Hi Linda,

    if your fiscal year is different than Jan 1st, you can change it in DPS App default settings:

     

Categories