-
1. Re: Creating a calculation using more than one date field
Chris Chalmers
Nov 30, 2018 1:14 PM
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
Chris Chalmers
Nov 30, 2018 2:13 PM
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