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!

[Ronny_39]

There are over 2 million records after the join multiple. 

There are a few customer-item-transaction type records with no Sales over last 6 months but Sales over last 12 months. Wouldn't inner join eliminate this type of record since there were no sales at 180 day mark? 

By grouping tool, do you mean 'Make Group' tool?

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

[Ronny_39]

Thanks! Can you share updated workflow with orders in the future excluded. 

[apathetichell · Accepted solution]

Does that mean the other version didn't crash?

[mtouiti]

Thanks @apathetichell for the help, amazing,

@Ronny_39 does that solve your issue ? 

[Ronny_39]

Thanks! @apathetichell. I was not able to resolve this issue completely with the workflow. Not sure how to fix this issue @mtouiti. 

[mtouiti]

hi @Ronny_39 , can you please share your expected output in a table format based on the data your have posted at the beginning ?

[Ronny_39]

Thank you both!! @apathetichell's solution worked.