2 Replies Latest reply on Jul 18, 2016 2:06 PM by Thy Nguyen

    Sum Total Sales after assign 1 and 0 to each Row

    Thy Nguyen

      Good morning,

       

      I have data of each Sales Order Number (SO Nbr) including transaction date, customer ID and sales amount. I don't want to look at each SO line individually, instead I want to group all the sales of 1 Customer (same customer ID) happening within 7-day-window as ONE DEAL. This action below was to count how many deals we have in a period of time. I created the Calculated Field called "Unique SO" that:

       

      IF abs(DATEDIFF('day',ATTR([Date]),LOOKUP(ATTR([Date]),1)))>7  THEN 1

      ELSEIF abs(DATEDIFF('day',ATTR([Date]),LOOKUP(ATTR([Date]),1)))<=7 THEN 0

      ELSE 1 END

       

      As you can see from the bottom to top, 1 is assigned to the first row with a date, 0 is assigned to the following row with the datediff <=7. If datediff is >7, 1 is assigned to that row. Of course this logic is restarting every Customer ID and at SO Nbr level Sorting by Date (from Min to Max). By counting all 1s (exclude 0), I know how many deals we have.

      [Deal] WIP Bookings by Deal.png

      My question is now I want to see how much amount of $ of Sales in EACH DEAL, so basically I have to sum the sales of rows with 1 and 0 (in 7-days). As you see here, I have 5 different DEALs (5 1s), how could I sum the amount of $ Sales in each DEAL? The purpose is to see how many deals with value < 1M, 1M - 2M, > 2M. I've been trying for 1 week, and am still stuck though.

       

      Please let me know if you have any questions.

       

      All help is much appreciated. Thank you so much!

      Thy