4 Replies Latest reply on Nov 30, 2018 2:13 PM by Chris Chalmers

# Creating a calculation using more than one date field

How do I calculate the following:

Account # 12345     Plantinum, customer as of : 2/18/18   Monthly sales:     jan 2018 \$7000,  Feb2018 \$8500,  Mar 2018 \$10500, Apr 2018 \$6500 May 2018: \$8800 Jun 2018 \$11000 Jul 2018 \$9500, Aug 2018 \$7500, Sep2018 \$7200, Oct2018 \$6200

I have a calculation that gives me the average \$ in sales for Jan - Oct 2018

However, how do I create a formula that gives me the average sales for this account based on when the date when they became a platinum customer?  So I want the calculation to give me the average of Feb-Oct

Thanks!

• ###### 1. Re: Creating a calculation using more than one date field

Hey Elvira,

This can be achieved many different ways depending on the viz you're trying to create. Try this: create a calculation called something like "Date is After Platinum" that returns true if the sales date of the current row is more recent than the date the customer became platinum:

[Sales Date] > [Platinum Date]

Then add it to Filters and filter out "false". Any calculations done on that sheet will now exclude dates before the customer became platinum.

-Chris Chalmers

• ###### 2. Re: Creating a calculation using more than one date field

I don't know how the columns look like but you try

AVG(if DATETRUNC('month',[Platinum Customer as of])>=DATETRUNC('month',[sales month]) then [Sales] END)

• ###### 3. Re: Creating a calculation using more than one date field

here is an idea of what the format of the workbook looks like. If I add the filter like you mentioned then it gets rid of the accounts that do not have a platinum date. So I think I have to try something else.

 Account # Platinum date Avg Sales Avg Sales since Platinum date 123456 2/18/2018 8270 456789 4/27/2018 10200
• ###### 4. Re: Creating a calculation using more than one date field

For "Avg Sales since Platinum date" try:

AVG(IF [Sales Date] > [Platinum Date] THEN [Sales] END)

Edit: I just realized this is almost exactly the same thing Maneesh suggested above . Credit to him.

-Chris Chalmers