+ Reply to Thread
Results 1 to 10 of 10

Restrict counting orders after date x ONLY to customers that also ordered before date x

  1. #1
    Registered User
    Join Date
    11-09-2012
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007
    Posts
    11

    Restrict counting orders after date x ONLY to customers that also ordered before date x

    Hi guys,

    I once again need your help. I use a sales database with one order per row (including order date). Another tab holds a lists of dates.

    What I want to do is to count the number of orders after each date in the second tab, but ONLY the orders from customers that also placed an order in a certain period running up to that same date.

    I attached an example worksheet. Say, I want to know the number of orders AFTER Jan. 3 from those customers that also ordered BEFORE AND INCLUDING Jan. 3. (Customers A, B and C before and including Jan. 3; they placed 2 orders after Jan. 3).
    Customer analysis EXAMPLE.xlsx

    Thanks in advance,

    Stijn
    Last edited by Stijn@hccnet.nl; 12-21-2012 at 10:52 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Restrict counting orders after date x ONLY to customers that also ordered before date

    Not immediately obvious if you're looking for total count or unique count ?

    Below is one approach

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    where Blad2!A2 holds date - eg 3rd January and Blad1 per sample file.

    NOTE: Above formula is not efficient so be wary of using on large ranges and/or in large quantity.

  3. #3
    Registered User
    Join Date
    11-09-2012
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Restrict counting orders after date x ONLY to customers that also ordered before date

    First off: works perfectly, thank you so much. Don't fully understand it, so in case you'd have the time to explain the elements, would love to know.

    Regarding the efficiency, my file is quite large in fact so the calculations are taking some time, but I had completely filled it up with sumproducts anyway, so nothing new in that sense.

  4. #4
    Registered User
    Join Date
    11-09-2012
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Restrict counting orders after date x ONLY to customers that also ordered before date

    Hmm, turns out it's actually too inefficient -- excel keeps freezing as I copy the formula to multiple cells. Any possibility of calculating it in a way less stressful on my pc (does not matter if it involves extra columns, worksheets or anything)?

    Thank you in advance.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Restrict counting orders after date x ONLY to customers that also ordered before date

    In light of prior post could you please provide us with a little more detail, i.e:

    - How big is your source data range in reality ?
    - How is your source data sorted ? (eg by date then customer or other)
    - Could you sort your data differently to now if desired ?

    - How many summary calculations are you performing (dates) ?

    There are a number of more efficient approaches you could adopt but answers to the above could be significant in terms of determining "which" of these approaches is most suited to your dataset.

  6. #6
    Registered User
    Join Date
    11-09-2012
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Restrict counting orders after date x ONLY to customers that also ordered before date

    Yeah ofcourse:

    - I have a sales database consisting of 19.831 products sold, divided over more than 10.000 orders. As I ultimately want to count orders, I created a column that uses a COUNTIF array formula to designate unique orders based on order number (therefore I adjusted your original proposed formula to sum this unique order column).
    - Right now the products sold are sorted primarily by customer value (= total amount spent), secondarily from old to new -- however, the file is set up so that sorting otherwise would not affect any of my calculations.
    - The number of summary calculations is about 800, but the secondary worksheet holds only a couple dozen UNIQUE dates. Regardless, calculating for just one date already takes about 2 minutes.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Restrict counting orders after date x ONLY to customers that also ordered before date

    Quote Originally Posted by Stijn
    I created a column that uses a COUNTIF array formula to designate unique orders based on order number (therefore I adjusted your original proposed formula to sum this unique order column).
    Given above a further sample file which reflects your actual setup might be helpful here - small dataset only (replace confidential values of course).
    If as implied you have 10-20k arrays on your source sheet this will have a significant impact on performance.

    In the interim...

    I'd suggest sorting your sales database by Customer and then Date (old to new).

    I would then be inclined to add a formula adjacent to your source data which would in pseudo-terms work along the lines of:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    where Col A holds customer and Col B date and C is the "helper" column.

    What the above will do is define the first date a client placed an order and assign this same value for all records for the same client.

    By sorting your data as outlined the above calculation is very (very) basic and thus very fast (no need for exact match etc).

    With the above calculation in place you can then find number of orders placed after a given date where same customer has placed orders on or before that same date courtesy of a standard COUNTIFS formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    where Blad2!A2 holds date - eg 3rd of January

    The COUNTIFS approach will be quicker than SUMPRODUCT as it's non-iterative.

    Note: following on from above if you do use SUMPRODUCT / Arrays be sure to keep precedent range sizes to a minimum / optimised at all times.

  8. #8
    Registered User
    Join Date
    11-09-2012
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Restrict counting orders after date x ONLY to customers that also ordered before date

    Hi DonkeyOte,

    Before I start: Happy holidays. Christmas dinners have prevented me from responding sooner.

    I have to apologize, as I've been caught not providing you all relevant information. Your last proposal is very clear and fast, thank you for that. However, I made the grave mistake of initially summarizing my issue as counting orders after date X by customers from before date X. For this issue, the helper column works perfectly, but what I would actually want to know is orders after date X from customers that also ordered in the period 3-8 weeks before date X. I think the helper column won't do here, as the relevant order could be that customer's first order or his hundredth.

    I attached a part of my file, personal data has been changed. In case this particular calculation can only be done with your orignal formula, so be it, but I seems as if my 2011 Intel i3 processor and 4 GB RAM are not going to cut it...
    Sales database SAMPLE.xlsb

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Restrict counting orders after date x ONLY to customers that also ordered before date

    Quote Originally Posted by Stijn@hccnet.nl
    I made the grave mistake of initially summarizing my issue as counting orders after date X by customers from before date X. For this issue, the helper column works perfectly, but what I would actually want to know is orders after date X from customers that also ordered in the period 3-8 weeks before date X.
    In reality the above removes the need of establishing the initial transaction and thus permits use of SUMIFS, COUNTIFS & AVERAGEIFS all of which will perform much better than an Array / SUMPRODUCT.
    (above assumes [given file format] that you may use formulas new to XL2007)

    Example:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In terms of rough performance indicator using just your sample file ... calculating col G (alone) whilst using SUM Array took approx 1.5 seconds whereas with SUMIFS that dropped to around 0.09 seconds.

    SUMIFS is not super-fast when used in massive volumes and/or with huge ranges but it will still be a significant improvement than your Arrays.

    If you're still having performance issues once modified let us know.

  10. #10
    Registered User
    Join Date
    11-09-2012
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Restrict counting orders after date x ONLY to customers that also ordered before date

    Yeah much faster, thank you! But my issue remains with one column: Summary!J.

    Right now, Summary!J3 holds the total number of orders since date Summary!F3, but what I need is the total number of orders since date Summary!F3 by those customers only that also ordered in that same 3 to 8 week period previous to date Summary!F3 relevant to the individual customer in Summary!D3 and Summary!E3. It is important to note here that the customers in Summary! are not all of the customers relevant to the calculation; Data! includes those in Summary!, but holds many more.

    This I was/am able to calculate with an adjusted version of your very first formula (of quite many by now, for which I cannot thank you enough), but I am afraid I stil do not see how it can be done with a 'lighter' calculation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1