In-Out - Comparison

[suby]

Hi All,

Comparing current month(30-04-2021) and Previous Month (31-03-2021)

On this I'm trying to find three scenarios.

- which ID have carried from Previous month to current month.
- which New ID have come in this current month.
- which IDs where there in Previous month but not present this current month

wanted this to be dynamic so that when more data is added this has to be dynamic to compare current vs previous month.

Attached the sample workflow.

[T_Willins]

Hi @suby,

This workflow will do the comparison.  The RegEx tool trims the day off of the Date field; the Sort tool puts the latest date on top for the Sample tool to take the first record, which will be the current month.  The first Join tool finds all records that match that date, then rejoins all the data on ID to find which ID's match (J Anchor = in current and previous months), which are only in the current month (L Anchor) and with are only in the previous month (R Anchor).  The Select tools in between the Join tools are just to limit the data, but are not needed to get to the correct records.

[Emil_Kos]

Hi @suby,

The most important thing is identifying dynamically the current maximum date(which is the current date) and a previous for a maximum date. Adding them into the data by append tool allowed us to use the formula tool to check if the date is current or the previous month.

The first filter tool is removing all the historical data that wasn't taken into consideration. I hope this helps. 

[Emil_Kos]

Hi @T_Willins,

I need to use regex next time instead of creating new date fields and manipulating them to identify the previous month.

I like it. 

HI @suby 

@T_Willins will work faster if you will have 2 months of data only. I did my best to make my solution still works if you will have more data inside but please check if it works correctly for more than 2 months.

[suby]

Thank you both for your solution it works on my sample data set scenarios.

But in real world in  my scenario i have combined multiple sheets with multiple excel files so that data is always gonna be more and just not only two months of data. any thoughts on multi row formula to flag all three scenarios.

On my sample Data set if i want to create a New Column and flag all three scenarios how can it be done ?  any thoughts on that please.

[DawnDuong]

hi @suby 

What you described seems like a vintage analysis that is often used in customer bridge, insurance triangle and banking credit assessment.

When I encountered those use cases, typically there are 2 steps:

1) First step is to combine the data into a mastersheet first where all the population of data that you want to examine is there.

2) Second step is to create a new column "prior month" for previous month which calculates the exact day 1 month earlier than the "current date"

3) Third step is to do a Join where both L and R inputs connect to the same input files. The join condition is Left ID = Right ID, "Current date" = "Prior month". 

4) Extract data from the output as follow:
- J: ID exists in both current month and prior month

- L: ID exists in current month, but not prior month > NEW

- R: ID exists in prior month, but not current month > Discontinued

Hope this works for you.
Dawn.

[T_Willins]

Hi @suby,

I just want to clarify to be sure we are taking the right track.  My solution looks at the current month and compares it to all previous months.  If this is what you need, it would be easy to tag the data as you mentioned.  The number of Excel sheets and files can be managed and doesn't change the evaluation.  Tagging all input data so that given month's IDs would be compared to the previous month's and subsequent month's IDs would most likely require a batch macro, but should be able to be done.  See below for a quick mock up of a multi-month evaluation (not yet an Alteryx data output).

Evaluate All Data Against Previous and Subsequent Months
Date	ID	New in Current Month	In Previous and Current Months	Not in Subsequent Month
28-02-2021	ID4	X		X
28-02-2021	ID5	X		X
31-03-2021	ID1	X
31-03-2021	ID2	X		X
31-03-2021	ID3	X		X
30-04-2021	ID1		X	N/A
30-04-2021	ID4	X		N/A

[suby]

Thank you  DawnDoung for you solution will give it a try now.

@T_Willins - In my scenario the comparison would be done for current Month and Previous Month Only

On my case it was for 30-04-2021 against 31-03-2021.

When we receive the Data for the Month of May end the comparison would be 

31-05-2021 against 30-04-2021.

Many thanks for your time.

m

[suby]

Hello DawnDoung,

Would you mind if i can ask you to mock up quick workflow using my sample data please.

Thanks

[suby]

Hello T_Willins,

Can you please share the workflow with just comparing the current and Previous month and making it dynamically please.