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.
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.
Hi Roland,
Thanks for this. It is now giving the following error message.
Filter (4) Parse Error at char(16): Unknown variable "Dates"
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"
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.
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).
Hope this clarifies the approach.
Best,
Roland
Dear Roland,
Only 12 records identified instead of 36 records. Not sure whether any further changes to be done at my end.
Regards
Sudharsanan
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:
Should other records be included as well?
Best,
Roland
Hi Roland,
I am looking for Q1 data (1st Jan 2021 to 31st March 2021).
Regards
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).
What do you think?
Best,
Roland
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