Dynamic pay period date range filtering

[tchar21]

Hello,

I'm looking to use Alteryx to dynamically filter for a range of dates that belong to specific 'pay periods', conditional on today's date. Each pay period is 14 days long and always starts on a Monday.

The complicating factor is that I want to be able to view the dates in a pay period up until the 'pay day' for that period, which comes 8 days after the last day in the pay period.

For example, if today's date is 2021/07/19 which is pay day for the blue pay period (see image and spreadsheet), I still want to see the blue pay period dates (2021/06/28 to 2021/07/11). The next day (2021/07/20), I want Alteryx to show the dates for the most current pay period (in green), from 2021/07/12 to 'today's date', 2021/07/20.

The range of dates displayed would then update to include each passing day until we hit the 14th day of the pay period (2021/07/25). For each day after the 25th, the date range would remain 'static' until the pay day for that period (2021/08/02) is passed. After pay day, Alteryx would display dates for the next pay period (in yellow), from 2021/07/26 to the current date, 2021/08/03. Each passing day would be added to the date range until the 14th day of the pay period. In the 8 days beyond that, the date range would be static, then refresh again when we pass pay date for the yellow period, and the pattern would continue.

I hope this makes sense. Thank you in advance for any assistance.

Regards

[apathetichell]

You can do something like this with a table tool where you set the color based upon the value in three empty fields- but I don't know if the result will be what you want. Like you'll just have dates and blocks of colors with empty fields which you won't be able to add data to or manipulate. Is that what you want? Once the data comes out of the table tool you won't be able to make any changes to it.

[ncrlelia]

Hi @tchar21 ,

Attached is my suggested solution. You can test it out with different 'Today' dates in the Formula Tool (40).

Hope it helps.

Cheers,

Lelia

[apathetichell]

oh - you want to see the data - right? I've seen alot of requests for weird render things. Sorry. oh man. Anyway - totally worthwhile project and sorry for the misunderstanding...