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

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.

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

• ###### 2. Re: Sum Total Sales after assign 1 and 0 to each Row

Hi,

Here comes the worksheet and sample data.

As you can see in the file "Tableau 1". I set up the "Unique Deal" calculating table base on SO Nbr and Restarting Every Customer ID. Now by counting all number 1 (Sheet 2), I can come up with number of Deal. I want to know how much \$ Amount associating with 1 Deal.

Thank you!

Thy