Altair RISE

A program to recognize and reward our most engaged community members

Nominate Yourself Now!

Past Few Days Data

I'm trying to automate a report, but am getting tripped up by the need to select the past few days data. I've been manually changing the selection criteria each time, but that won't work with automation.

I thought one approach could be to create a formula field which returns "Review" if the create_dt is within the past ten days, then do a selection criteria to only select the ones that say "review" in that column, but the query I'm trying to create isn't acceptable. If(create_dt.In.(Today(),-10)),"Review","")

I feel like I'm missing something really basic as this has got to be a common query. Does anyone have time to give me some ideas? Thanks!

Find more posts tagged with

English

Monarch

Accepted answers

All comments

Altair Forum User

Always more than one way to do something in Monarch. Here is one possibility. If you just want to see the data, you can create a filter using the Age() and Date() functions:

Age([Report Date],Date(),4)<10

Age will return a number between two dates ([Report Date] is a field I pull in, and Date() is a monarch function that returns today's date). The number that is returned depends on the last parameter (4). In this case, 4 says to return the number of days. Other options are years, months, weeks, hours, minutes, seconds (the help file has more detail).

Since this is a filter, I added the <10 to only show my records that have a report date that is less than 10 days old. Hope this helps, or if I did not understand the question, let me know.

Altair Forum User

Always more than one way to do something in Monarch. Here is one possibility. If you just want to see the data, you can create a filter using the Age() and Date() functions:
Age([Report Date],Date(),4)<10
Age will return a number between two dates ([Report Date] is a field I pull in, and Date() is a monarch function that returns today's date). The number that is returned depends on the last parameter (4). In this case, 4 says to return the number of days. Other options are years, months, weeks, hours, minutes, seconds (the help file has more detail).

Since this is a filter, I added the <10 to only show my records that have a report date that is less than 10 days old. Hope this helps, or if I did not understand the question, let me know.

Thanks Chris. I like where you're going with this except I don't know how do create a filter based on a formula. When I do the "apply filter" at the column level, I only have the preset options of "multiple selections", "before", "after" and "range" - where within the app do you go to filter by a formula?

Altair Forum User

Thanks Chris. I like where you're going with this except I don't know how do create a filter based on a formula. When I do the "apply filter" at the column level, I only have the preset options of "multiple selections", "before", "after" and "range" - where within the app do you go to filter by a formula?

Are you using Monarch Classic or Monarch Data Prep Studio? Also, what version?

Altair Forum User

Are you using Monarch Classic or Monarch Data Prep Studio? Also, what version?

DataPrep Studio, v 14.3.2.15407

Altair Forum User

DataPrep Studio, v 14.3.2.15407

Ok. My previous response would work in Monarch Classic. For Data Prep, there are a couple of more steps.

Create a new field (Create Calculated Field). In the box for Field Name give it a more useful name other than "Formula Field", i.e. "Review". Modify the formula I sent earlier to (basically dropping off the <10):

age(Date,date(),4)

This will create a new column called "Review" and list the number of days from today's date.

Now, click on the down arrow for the "Review" column and select Apply Filter...:

Click on the Blue button in the upper right to select the filter type "Less Than":

Fill in the Less than box with the number of days you want to review. You can also select the checkbox on this screen if you want to include (less than or equal to) that number of days.

Altair Forum User

Ok. My previous response would work in Monarch Classic. For Data Prep, there are a couple of more steps.
Create a new field (Create Calculated Field). In the box for Field Name give it a more useful name other than "Formula Field", i.e. "Review". Modify the formula I sent earlier to (basically dropping off the <10):
age(Date,date(),4)
This will create a new column called "Review" and list the number of days from today's date.
Now, click on the down arrow for the "Review" column and select Apply Filter...:
Click on the Blue button in the upper right to select the filter type "Less Than":
Fill in the Less than box with the number of days you want to review. You can also select the checkbox on this screen if you want to include (less than or equal to) that number of days.

Woohoo! I may not have followed your thoughts exactly, because like you said, there's more than one way to get there, but your last response triggered an idea similar to yours and I got where I needed to be.

What I did was create the calculated field to determine the age of the row (today minus create_dt), and than filtered out anything over ten days old, so that I'll always have the last ten days worth of data.

Thank you so much for spending the time to help on this today. Enjoy your weekend!