Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Formula for selecting date range

gsudharsanan
7 - Meteor

Dear Community / Experts,

 

The file having data for many years. Date in YYYY-MM-DD format.  What formula to be used to filter only items relating to the previous quarter. For eg:- If running the query in April 2021 it should pick up only date range 1st Jan 2021 to 31st March 2021. Similarly, when running the query in July 2021 it should pick up only date range 1st April 2021 to 30th June 2021. 

 

Seek your help / support. 

 

Sample file attached. 

21 REPLIES 21
gsudharsanan
7 - Meteor

Dear

 

Now it gives the following errors:-

 

 Error: Filter (6): The field "Dates" is not contained in the record.

Error: Filter (8): The field "Dates" is not contained in the record.

 

Please note that in my file the date is under column 8 and not in the first column. do i need to make any changes because of this. 

gsudharsanan
7 - Meteor

Hi Roland,

 

Thanks for this. It is now giving the following error message.

 

Filter (4) Parse Error at char(16): Unknown variable "Dates"

Ar13f
8 - Asteroid

dear @gsudharsanan 

 

what is the actual column name for the "dates" column? for example the column name is "YYY" please replace the "dates" column in all formulas with "YYY"

gsudharsanan
7 - Meteor

Dear,

 

Thanks, this helps.  However, in the end result both T & F is reflecting Qtr values as 2. If my understanding is correct, one value should be 1 (first quarter) and other value should be 2 (2nd quarter). Kindly advice. 

RolandSchubert
15 - Aurora
15 - Aurora

Hi @gsudharsanan ,

 

I used the data file you provided, the header is "Dates" (specified in Input Data tool, that first line contains header).

The Formula tool calculates the range to filter (first and last month of previous quarter, in case, month is Jan-Mar,

previous quarter is Q4 prev. year (e.g. in Feb 2021 prev quarter is Oct-Dec 2020).  

 

 2021-09-14_14-42-04.jpg

 

Hope this clarifies the approach.

 

Best,

 

Roland

gsudharsanan
7 - Meteor

Dear Roland,

 

Only 12 records identified instead of 36 records.  Not sure whether any further changes to be done at my end.

 

Regards

Sudharsanan

RolandSchubert
15 - Aurora
15 - Aurora

Hi @gsudharsanan ,

 

I'm confused - today is 2021-09-15, last quarter in Q2 (2021-04-01 to 2021-06-30). There are only 12 records within this range in your dataset:

 

2021-09-15_16-40-47.jpg

 

Should other records be included as well?

 

Best,

 

Roland

gsudharsanan
7 - Meteor

Hi Roland,

 

I am looking for Q1 data (1st Jan 2021 to 31st March 2021).

 

Regards

RolandSchubert
15 - Aurora
15 - Aurora

Hi @gsudharsanan ,

 

okay, I see ... I used the current date (DateTimeToday) to find the previous quarter (i.e. Q2 now). 

 

To get Q1, you can modifiy the "-1" (in July, the last closed month is June,  to get the last month I subtract 1 month) to "-4" (would mean, go back one month and one quarter).

 

2021-09-15_19-46-46.jpg

 

What do you think?

 

Best,

 

Roland

gsudharsanan
7 - Meteor

Hi Roland,

 

Yes, it works. Thanks a lot for your guidance & support.  One more clarification. Does this mean when i run this query every quarter need to manually change the numbers as given above? I was looking for a automated solutions (without making changes every quarter should be able to run this every quarter and it should only fetch the previous quarter results). Kindly advice.

 

Once again thanks a lot for your guidance.

Regards

 

Labels