Calculate Last 6 Months and 12 Months Sales Data Based on Current Date, Customer & Item Id

Hello Alteryx community,

I need to calculate sales (last 6 months and last 12 months) for every unique customer name- item- transaction type combination in my dataset. Dataset has multiple rows with same customer name and item number but different invoice date.

Last 6/12 months sales calculation will be based on current date(today's date).

6 months (i.e. last 180 days Sales) will include all sales between 26th January 2021 and 25th July 2021.

Dataset -

Item Customer Invoice Date Invoiced Quantity Transaction Type

1 ABC 1st July, 2021 50 T1

2 ABC 1st Dec, 2020 100 T2

3 CDE 1st Aug, 2021 20 T3

1 ABC 1st October, 2020 25 T1

2 CDE 1st May, 2020 75 T2

Example ---

For customer ABC, item 1 and transaction type T1, Last 12 months sales will be = 50+ 25 = 75 units

For customer ABC, item 1 and transaction type T1, Last 6 months sales will be = 50 units.

I'm new to Alteryx. I could not figure a way to build this complex condition in Alteryx.

Your help is appreciated. Thanks!

Workflow

Accepted answers

apathetichell

Does that mean the other version didn't crash?

ronny sales project.yxmd

All comments

apathetichell

so your first problem is that you need to convert into dates... second is you'll need to group if a date falls within the category you want (ie 180 days, 360 days or na) you can do this via two formula tools which check the value of datetimediff([datetimetoday(),[yourdatefield],"days")<= 180 and datetimediff([datetimetoday(),[yourdatefield],"years')<=1 and then you can drop summarize tools where you group by customer name/item/transactiontype and the new groups to get the sums.

If this doesn't quite make sense - try posting some data...

mtouiti

Hey Ronny,

Here is the workflow that calculates the sales quantities per dates.

Best

Ronny_Sales.yxmd

Ronny_39

Thanks apathetichell! I used the data formula you provided. I'm not able to summarize data properly.

For 2 formulas, I want to summarize the results and ensure output file contains 2 unique columns for Sales - 1 for 180 days and the other for 360 days without duplicating results.

Ronny_39

Thanks mtouiti for the workflow. I was able to get 180 days and 360 days using a variation of the workflow you provided.

However, I'm not able to summarize sales data using Union without creating multiple rows for the same customer-transaction type-item combination.

Union tool is creating multiple rows for 180 days and 360 days sales for the same customer, item and transaction type.

Ideally, output should contain only 1 row for the same customer-item and transaction type . The same row should have 180 days and 360 days sales number,

apathetichell

Yeah - that's what I figured. I aggregated by customer and Item and rejoined... I cut a ton of steps on my date conversion so it might be harder to follow and I stole @mtouiti 's input file...

Ronny_Sales - stole mo's data.yxmd

Ronny_39

Thanks apathetichell!! I tried to join using workflow you attached. But I keep getting "Number of records exceeds Excel maximum". When I used output data separately for 180/360 days sales, it worked fine with total records being 50K.

After adding join to the workflow I'm getting this error. Not sure why as records are not crossing Alteryx excel export limit.

apathetichell

Here's a slightly revised version--- if you can't get the line data to work - perhaps the summary data will. note - orders in the future are counted - let me know if you want me to exclude them. Also 1 year is set up as 1 year - not 360 days.