Comparison - Previous Month Data

[suby]

Hello All,

In my dataset i have 3 months of Data combined together.

Say my current Reporting month is 30/04/2021.

I want to create two additional column with flags called PRODUCT FLAG and SUB PRODUCT FLAG.(Comparison is Done against 30/04/2021 and 31/03/2021)

On the New PRODUCT FLAG Columns i want to Check if the PRODUCT exist on my previous month then Flag it as Existing else New Product and the same with SUB PRODUCT FLAG.

Finally i want to keep this dynamic so when i add 31/05/2021 data the comparison should be done  against 31/05/2021 with 30/04/2021

Attached the workflow.

Thanks

[atcodedog05]

Hi @suby 

Here is how it can be done

Workflow:

1. Using summarize tool groupby product & sub product and get count of dates. Logic behind is if the count of months for a product & sub product is greater than 1 then its an existing if not new.

2. Using join tool joining on product & sub product to map count.

3. Using formula if [count]>0 existing if not new. And if sub product is null then No associated Sub product.

Hope this helps 🙂

[suby]

Hi atcodedog,

Thanks for your solution will this work when i add my 31/05/2021 will it automatically compare 31/05/2021 with 30/04/2021 ?

Thanks

[atcodedog05]

Hi @suby 

It will it will just check number of months and not which months. So should work.

Edit : provided only 3 months data is present.

If not let me know.

[suby]

Hi atcodedog,

Thanks for your solution but there are few things which i needs to considered

- There could be scenarios

1) A new product or sub product may be added in April but not in march ( this is where your selection works).

2)  like  a product or sub product  which might present in March but not in April so i need to consider these scenarios.

3) at the moment in my example we are comparing only the product  ID and sub product but there will be additional scenarios for me to check or in include additional conditions like Product Status and Sub Product status as well to check. ( say for an example like how many new product or sub product  with status 'OPEN'  has came in for the Month of April compared to previous month like so).

Can help me with scenario 2 and 3.

Thanks