Formula to select rows based on paritions

[champ24]

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?

[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

[atcodedog05]

Hi @champ24 

Here is a workflow for the task.

Input: given file

Output:

As your expected but in your output for 2nd ID a222 there is 6 rows instead there should be 10 rows based on input can you check.

Workflow:

Hope this helps 🙂

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

[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

[champ24]

for the above attached dataset the workflow is working partially. Is it possible to achieve desired output in alteryx?

[atcodedog05]

Hi @champ24 

I will check and get back to you.

[Qiu]

@champ24 
While @atcodedog05  takes the break, I would like share my thoughts on this.
And maybe I misinterpret what you want, I am getting something different with your out, unless the sample data is not updated?

I manually checked, and think maybe I am correct.

[atcodedog05 · Accepted solution]

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 😀👍

[grazitti_sapna]

Hi @champ24 , give this workflow a try.

Thanks.