4 Replies Latest reply on May 18, 2016 4:27 AM by Prateek Gupta

# Calculate based on date range

I have two tables, in this format

table 1:

Seller
MarginDate From

A

20%

1-Apr-16

B10%10-Apr-16
B20%1-May-16
B30%10-May-16

Table 2

SellerOrder DateSale Value
B

8-May-16

1000
A20-May-162000

When I join these, I am trying to get commission value for each seller. Seller is not an issue, for seller B:

I need the calculation to be 1000*20% = 200.  (20% margin was applicable for all orders from 1st may to 10th May)

Can someone please help me on how to go about this in tableau ?

• ###### 1. Re: Calculate based on date range

What is your datasource? SQL? Excel? something else?

• ###### 2. Re: Calculate based on date range

Using a MySQL database.

• ###### 3. Re: Calculate based on date range

Ok, I suggest you join the tables in SQL before pulling in to Tableau. First I would build a view on Table2 to bring in a DateTo field - it'll make the join with Table1 more straightforward. For example:

Seller

Margin

Date From

DateTo

A

20%

1-Apr-16

B10%10-Apr-161-May-16
B20%1-May-1610-May-16
B30%10-May-16

Your SQL statement bringing the data into Tableau will be along the lines of:

SELECT t2.*, t1.Margin

FROM ViewOfTable2 t2

JOIN TABLE1 t1 ON t2.Sellet = t1.Seller AND T2.OrderDate >= t1.DateFrom and t2. OrderDate < ISNULL(t1.DateTo,NOW())

• ###### 4. Re: Calculate based on date range

Thanks a lot Andrew. Adding the DateTo along with the formula given works like a charm.