Altair RISE

A program to recognize and reward our most engaged community members

Nominate Yourself Now!

How to extract data from most recent quarter

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

Find more posts tagged with

English

Monarch

v2023.0

Accepted answers

All comments

Baba_Majekodunmi_703

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?

lsojat

Yes to extract data that happened in that current quarter

Baba_Majekodunmi_703

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.

lsojat

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

Mo Abdolrahim

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.

Current_Quarter.txt

Mo Abdolrahim

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

By the way you change the fiscal year if in DPS

lsojat

Testing it out now-thanks so much

Mo Abdolrahim

Hi Linda,

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

Screenshot 2024-03-14 140855.png