• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search 12 months backwards (rolling) for transactions for same item # > 0

chocolatedogs

New Member
I uploaded a file for reference. I need the total # of Item ID's per month that did not have a Transaction for the past 12 months. Item ID's are not unique – there can be multiple transactions back in time for the same item ID. Transaction date data goes back 8 years, and includes multiple transactions on the same date. Transaction amounts vary – the value is not important, just if there is a value > 0 at all. It needs to be a rolling 12 months, starting on the latest date and searching backwards in the data.

The data contains numerous individual day dates, but needs to be summarized by month. For example, for transactions in June 2016 I want to look back through July 2015 and see if there are any transactions for the same Item ID with a value > 0. I don't need to do any calculations on them, and I do not need to search for a certain value, just if there is one or not.

You can think of having no Transaction values > 0 for the past 12 months as what we don't want. We want customers to come back in and buy more product from us – if they have not bought in the past year they are considered a lost customer.

The only VBA I have done was simply from the Macro recorder, i.e. I am not readily familiar with VBA programming and would like to do this without VBA, though I suspect that is not possible.

Any thoughts or comments greatly appreciated - I honestly don't know where to start on this.

Julie
 

Attachments

  • Transaction Data File.xlsx
    41.6 KB · Views: 3
Hi Julie ,

The problem is not really so complex ; the main point is whether you want the formula to automatically calculate the FROM and TO dates , or can you enter these two dates in two worksheet cells ?

If this is possible , then a straightforward formula such as :

=SUMPRODUCT(($A$3:$A$22 = A3) * ($B$3:$B$22 >=$E$1) * ($B$3:$B$22 <= $E$2) * ($C$3:$C$22 > 0))

will tell you whether there has been a transaction with a non-zero amount between the two specified dates , where the cells E1 and E2 contain the dates.

Narayan
 
Hi chocolatedogs,

If you need rolling sum of Transaction amount per Item ID than you can do two things-

1) Pivot table with "Group" (right click on column label and choose day / month / year as per your convenient)

upload_2016-7-23_8-31-30.png

2) use formula -

2.1 sumproduct -formula shared by Narayan,
2.2 you can also use sumifs (easy to use)

I uploaded a file for reference. I need the total # of Item ID's per month that did not have a Transaction for the past 12 months. Item ID's are not unique – there can be multiple transactions back in time for the same item ID. Transaction date data goes back 8 years, and includes multiple transactions on the same date. Transaction amounts vary – the value is not important, just if there is a value > 0 at all. It needs to be a rolling 12 months, starting on the latest date and searching backwards in the data.

The data contains numerous individual day dates, but needs to be summarized by month. For example, for transactions in June 2016 I want to look back through July 2015 and see if there are any transactions for the same Item ID with a value > 0. I don't need to do any calculations on them, and I do not need to search for a certain value, just if there is one or not.

You can think of having no Transaction values > 0 for the past 12 months as what we don't want. We want customers to come back in and buy more product from us – if they have not bought in the past year they are considered a lost customer.

The only VBA I have done was simply from the Macro recorder, i.e. I am not readily familiar with VBA programming and would like to do this without VBA, though I suspect that is not possible.

Any thoughts or comments greatly appreciated - I honestly don't know where to start on this.

Julie
 
Thanks for the quick and comprehensive answers - they get me a long way towards the final solution.

The formula needs to calculate the 12 months - it is a rolling backwards look. There are 1000's of rows of data so I can't really enter dates in column E. I don't think I explained that very well.

Thanks for any help you can offer.

Julie
 
Hi ,

There is no need to enter dates in column E ; you need to enter exactly two dates , a from date and a to date.

In the formula I posted earlier , which is :

=SUMPRODUCT(($A$3:$A$22 = A3) * ($B$3:$B$22 >=$E$1) * ($B$3:$B$22 <= $E$2) * ($C$3:$C$22 > 0))

the from date needs to be entered in cell E1 , and the to date needs to be entered in cell E2.

In E2 , you can , if you want to , just have the formula : =TODAY() , while in E1 , you can have another formula , such as :

=EDATE(E2 , -12)

or

=E1 - 365

or any such formula , if you do not wish to be accurate.

If you want accuracy , then you need to define what it means to be exactly one year back from today's date , based on which a suitable formula can be entered in E1.

If you do not wish to use these two additional cells , the same formulae can be incorporated within the SUMPRODUCT formula.

Narayan
 
Thanks for all the help guys - I really appreciate it more than you know. Based on all the great info I have focused on dates and the 0 value transactions alone since those are what really tells me if a customer is lost. I pared the transaction data down as much as possible, but it still is over 41,000 rows.

I have had a steep learning curve with SUMPRODUCT and I am still learning about/struggling with working with extremely large datasets (never worked with either before - I know just enough about Excel to be dangerous :))

Again, much gratitude for taking your time to help me - if I study Excel for the rest of my life I will still be learning new things every day!

Julie
 
Back
Top