Dynamic select latest 6 months data

[wenyg]

Hi, if i have a list of trailing 12 months' data (Nov 2022 - Oct 2023), now i want to extract the lastest 6 months' data (May 2023 - Oct 2023) every time i run the workflow?

[Deano478]

@wenyg my way of doing this would be using 2 filters one after another with the following expressions:

Filter 1 - 

[DateField] >= "2022-11-01" AND
[DateField] <= "2023-10-31"

Filter 2:

DateTimeDiff([DateField], DateTimeNow(), "months") >= -6

Then just throw down a sample tool to skip the first 9 rows.

[wenyg]

so my data would look like this, can you advise on how to extract the lastest 6 months based on these 3 columns?

[caltang]

First, I would get the Month Year. 

Make a new field using Formula tool and try this:

Field Name -> Month Year

Data Type: V_WString

Data Size: 255

Expression: ToString([Period]) + ToString(Right([Year Dim],4))

Then, use the DateTimeNow function or tool to get the current date today, and add a formula tool to get the Month Year as well. 

In this case, make it a string type then use DateTimeParse([DateTimeNow],”%b %Y”) to get you Month Year. Then, you can make it dynamic by subtracting 6 months in your Filter tool. 

You don’t need a Dynamic Select for this case since your data is in the records and not the fields. 

Im on my phone right now so I can’t help much other than provide theory above

[TimN · Accepted solution]

This may work.