Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

In-Out - Comparison

suby
11 - Bolide

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.

12 REPLIES 12
T_Willins
14 - Magnetar
14 - Magnetar

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.

 

In-Out.png

Emil_Kos
17 - Castor
17 - Castor

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_0-1623543214958.png

 

Emil_Kos
17 - Castor
17 - Castor

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
11 - Bolide

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
13 - Pulsar
13 - Pulsar

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
14 - Magnetar
14 - Magnetar

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
DateIDNew in
Current Month
In Previous and
Current Months
Not in Subsequent
Month
28-02-2021ID4X X
28-02-2021ID5X X
31-03-2021ID1X  
31-03-2021ID2X X
31-03-2021ID3X X
30-04-2021ID1 XN/A
30-04-2021ID4X N/A
suby
11 - Bolide

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
11 - Bolide

Hello DawnDoung,

 

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

 

Thanks

suby
11 - Bolide

Hello T_Willins,

 

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

Labels