Show Gross and Net Count of Changes of Records

[kat53191]

Good Morning,

I am hoping someone can help me think through the most efficient way to do this.  Basically, I have a list of SKUs from our monthly inventory.  What I want to see is the count of NEW (+) SKUs, count of DELETED (-) SKUs, and calculate the net change to equal the total count of SKUs per month.  Below is some dummy data that shows what the input table would look like and my desired output:

Input
Month	SKU
Jan	Bag
Jan	Purse
Feb	Bag
Feb	Purse
Feb	Hat
Mar	Purse
Mar	Hat
Mar	Glove

Desired Output
Month	+	-	Net Change	Total Count
Jan	2	0	2	2
Feb	1	0	1	3
Mar	1	-1	0	3

Appreciate your help!

Thanks

[Thableaus · Accepted solution]

Hi @kat53191 

See if this works (attached).

I don't have much time to explain the solution but basically you need to create a table with all possible combinations of SKU and Month, do a Count, and then compare the net change for each combination across the months.

You also need to Parse the month name to a month number so you can keep track of time. If this is a year month combination, no worries, you can parse it to YearMonth and sort them correctly.

Let me know what you think.

Cheers,

[gabrielvilella]

Hi @kat53191, you may need an iterative macro for this. Here is how to do it.

[RolandSchubert]

Hi @kat53191 ,

my approach looks like this:

IT's based on comparing SKUs in a month's  inventory with last/next month inventory.

Let me know if it works for you.

Best,

Roland

[kat53191]

@Thableaus This worked for me!  Thank you so much!