Filter column - last month of previous quarter

[amadra]

Hello community,

I have a dataset where column headers are dates (one column per month).
Based on the column names - I want filter the column which is the last month of the previous quarter.

I managed to write a formula to derive the last month of the previous quarter using dynamic select - added in the workflow attached for your reference.
The formula uses DatetTmeNow function. It worked perfectly in June (it showed March as last month) but now in July its showing the June as the last month of previous quarter - which is technically correct but not what I want.

I want to filter the last month of previous quarter using the data (i.e. column headers) that I have and not the DateTimeNow function.

Please see workflow attached.

[PhilipMannering]

Hi @amadra 

This was something of a challenge....

Perhaps this works,

startswith([name], tostring(
if datetimemonth(datetimetoday()) < 4 then datetimeyear(datetimetoday()) - 1 else datetimeyear(datetimetoday()) endif    // year
) + '-' +
padleft(
tostring(
(floor(mod(tonumber(datetimemonth(datetimeadd(datetimetoday(), -4, 'month'))), 12) / 3) + 1)                             // previous quarter
* 3 // last month
), 2, '0'))

[amadra]

Hi @PhilipMannering - The calculation you did using DateTimeToday() does the same thing that I do using DateTimeNow(); and gives "2020-06-30" as the last month of the previous quarter.

I am looking for a way to filter the column based on the data available (i.e. column headers) and not using DateTimeToday/DateTimeNow.
For example - if the data set is from January to June, regardless of the month we are currently in - it should give "March" as the last month of previous quarter.
Example2 - if the data set is from January to September, it should filter "June" as the last month of previous quarter.
Example3 - if the data set is from January to November, it should filter "September" as the last month of previous quarter.

Please see the desired output in the workflow for reference.

[binuacs]

@amadra 

[soccertil1108]

Transpose the data to get the columns as fields. Find the Max value of the dates. [Perform the calculation @PhilipMannering provided]. Append the required date onto the records. Filter out the required field. Cross Tab back again and perform further work.

[binuacs · Accepted solution]

@amadra one way of doing this

[amadra]

@binuacs - brilliant! Your solution works for most of the months, except the first quarter (where we only have three or less months in the data set) - where it gives blank. I want the last month in the data set for the first quarter but I figured it out.

Thanks for taking the time to solve this! 😄