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

[Ronny_39]

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!

[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

Mo

[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, 

[mtouiti]

 in this case, add a Cross Tab tool to have one line per customer and Trans Type. 

below the configuration needed and the result : 

Best

Mo

[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_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. 

[Ronny_39]

Thanks Mo. I created 2 new fields - last 180 and last 360 days sales units. Seems cross-tab allows only 1 column change. Is there any other workaround I could use?

[apathetichell]

hmmmmm.... can you see how many records there are after the join multiple??? That could be part of the problem or we aren't doing the join multiples on enough columns - or perhaps don't need an outer join and should only be doing an inner? If you have that many records I'd probably swap my first filter by 180 to a filter by 360, and I'd probably add a grouping tool to my 180 boolean. The other entries should be 0 but having a record there means that I can join that directly with the other stream. Then I can just use inner joins to wheedle down my data. Or perhaps you can just get a top level analysis grouped by the info? I have it set up to rejoin to the original data but perhaps you just need the summary?