Formula to select rows based on paritions

Hello,

I am trying to select data if a month exists in a given date range. The formula I am using in the filter tile is:

([YEAR]=datetimeyear([START_DATE]) or [YEAR]=DateTimeYear([END_DATE])) and ([MONTH] >=DateTimeMonth([START_DATE]) and [MONTH]<=DateTimeMonth(END_DATE]))

It works if a person has only one single date range. But if a person has multiple date ranges then its not excluding data correctly. Below is the snapshot of input and expected output. Months 5,6,7 doesnt exist in the start_date and end_date ranges and those rows shd be the output.

Is this possible?

Workflow

Datasets

Developer

Output

Tips and Tricks

Date Time

Transformation

Join

Iterative Macro

Accepted answers

atcodedog05

Hi @champ24

Here is the modified workflow the task.

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

month.yxmd

champ24

I created the workflow and seems to be working correctly. The only way it seems to be possible is to exclude the specific rows and handle separately through a couple of filters and then union.

month_solution.yxmd

All comments

atcodedog05

Hi @champ24

It is definitely possible can you please provide sample input data. Can prep up the workflow.

champ24

Sure. Sample Data file is attached.

Please note Month is based of below format (Fiscal year/month):

15-DEC-2019' and '14-JAN-2020' then month is 1

'15-JAN-2020' and '14-FEB-2020' then month is 2

'15-FEB-2020' and '14-MAR-2020' then month is 3

'15-MAR-2020' and '14-APR-2020' then month is 4

'15-APR-2020' and '14-MAY-2020' then month is 5

15-MAY-2020' and '14-JUN-2020' then month is 6

'15-JUN-2020' and '14-JUL-2020' then month is 7

'15-JUL-2020' and '14-AUG-2020' then month is 8

'15-AUG-2020' and '14-SEP-2020' then month is 9

'15-SEP-2020' and '14-OCT-2020' then month is 10

15-OCT-2020' and '14-NOV-2020' then month is 11

'15-NOV-2020' and '14-DEC-2020' then month is 12

Below is expected output.

OUTPUT
ID	Year	Month	Name
a111	2020	5	Randle Wood
a111	2020	6	Randle Wood
a111	2020	7	Randle Wood
a222	2020	1	Barista John
a222	2020	2	Barista John
a222	2020	3	Barista John
a222	2020	4	Barista John
a222	2020	5	Barista John
a222	2020	6	Barista John
a333	2020	1	Scott Lenn
a333	2020	5	Scott Lenn
a333	2020	6	Scott Lenn
a333	2020	7	Scott Lenn
a333	2020	8	Scott Lenn
a333	2020	9	Scott Lenn
a333	2020	10	Scott Lenn

Thanks

sample data.xlsx

champ24

Thank you. I just realized there are a couple of situations that I shd have included earlier. I have updated the sample data file.

Expected output shd be:

a444	2020	4	Shawn B
a444	2020	5	Shawn B
a444	2020	6	Shawn B
a444	2020	7	Shawn B
a444	2020	8	Shawn B
a444	2020	9	Shawn B
a444	2020	10	Shawn B
a555	2020	1	Zen A
a555	2020	2	Zen A
a555	2020	3	Zen A
a555	2020	4	Zen A
a555	2020	5	Zen A
a555	2020	6	Zen A