6 Replies Latest reply on Jun 16, 2016 12:10 PM by P N

# Summing Revenue for a Month Based on a Point in Time Date

I am trying to sum transaction revenue based on a minimum start date for each respective Company among their divisions.  Below is the data set and desired outcome.

 Dates Table Supplier Buyer Division Start Date Supplier A Company 1 Division 1 1/15/2016 Supplier A Company 1 Division 2 1/25/2016 Supplier B Company 1 Division 3 3/15/2016 Supplier A Company 2 Division 1 2/7/2016 Supplier A Company 2 Division 2 3/15/2016 Supplier B Company 2 Division 2 3/2/2016

 Transaction Data Table Tranactions Date for Transaction Dollars Supplier Buyer Division 1/15/2016 1/20/2016 1/25/2016 1/31/2016 2/7/2016 2/20/2016 2/28/2016 3/2/2016 3/15/2016 3/31/2016 Supplier A Company 1 Division 1 \$100 \$125 \$ - \$78 \$ - \$ - \$ - \$ - \$ - \$ - Supplier A Company 1 Division 2 \$ - \$ - \$200 \$55 \$ - \$ - \$ - \$ - \$ - \$ - Supplier B Company 1 Division 3 \$ - \$ - \$ - \$ - \$ - \$ - \$ - \$ - \$150 \$195 Supplier A Company 2 Division 1 \$ - \$ - \$ - \$ - \$175 \$112 \$37 \$ - \$121 \$127 Supplier A Company 2 Division 2 \$ - \$ - \$ - \$ - \$ - \$ - \$ - \$ - \$212 \$125 Supplier B Company 2 Division 2 \$ - \$ - \$ - \$ - \$ - \$ - \$ - \$323 \$ - \$545

We have identified the right start date with the formula:

{Fixed [Supplier], [Company]: Min([Start Date]) }

But are stuck on how to sum the revenue

We only care about when the Supplier's revenue in the month of the start date with the Company and not the Division.  However, the start date data is tied to the Divisions in our data source and then we have another table that ties Divisions to Companies and then another table with the transaction date.  The desired out put would be as follows:

 Desired OUTCOME First Month's Revenue Supplier Buyer Start Date January February March Supplier A Company 1 1/15/2016 \$558 \$ - \$ - Supplier B Company 1 3/15/2016 \$ - \$ - \$345 Supplier A Company 2 2/7/2016 \$ - \$324 \$ - Supplier B Company 2 3/2/2016 \$ - \$ - \$868

We have tried the following but it does not work:

{Fixed [Supplier], [Buyer]: IF DATEDIFF( 'month' , [Start Date],[Transaction Date])) = 0

THEN [Revenue]

END }

Attache is excel that visualizes the data sets.

• ###### 1. Re: Summing Revenue for a Month Based on a Point in Time Date

Hi Robert,

I think your main hurdle is the way you store your transaction data.  You can get pretty close to your desired view by pivoting the transaction data into a more normal format, the dates table is not needed.

You would want to pivot all the dates and then filter out all the null amounts.   You could end up with a view like below, you won't be able to get Supplier, Buyer in the order you want as Tableau groups data.  But the best advice I can give you is store your transaction data in a normal way.

Regards,

Ivan

• ###### 2. Re: Summing Revenue for a Month Based on a Point in Time Date

Thanks Ivan.  We have the transaction data stored in a normal way for Tableau.  I just displayed in excel for easier presentation purposes.  What is your "calculation1" in your screen shot?

• ###### 3. Re: Summing Revenue for a Month Based on a Point in Time Date

Hi Robert,

That's good you have your trans data stored normally, Tableau works much better with normal data sources and you won't need to pivot the data.

I'm assuming your transaction data is stored something like the example below.  Your LOD looks good but for this data it would be {Fixed [Supplier], [Company]: Min([TransDate]) }, I named dimension Start Date.

Now we create the measure ZN(SUM(if MONTH([TransDate])=MONTH([Start Date]) then [Amount]  end))

You then need to lay out the data as in the screenshot above and you should be good.  Please let me know if you have any questions.

Regards,
Ivan

 Buyer Division Supplier TransDate Amount Company 1 Division 1 Supplier A 1/15/2016 100 Company 1 Division 1 Supplier A 1/20/2016 125 Company 1 Division 2 Supplier A 1/25/2016 200 Company 1 Division 1 Supplier A 1/31/2016 78 Company 1 Division 2 Supplier A 1/31/2016 55 Company 2 Division 1 Supplier A 2/20/2016 112 Company 2 Division 1 Supplier A 2/28/2016 37 Company 2 Division 1 Supplier A 2/7/2016 175 Company 1 Division 3 Supplier B 3/15/2016 150 Company 2 Division 1 Supplier A 3/15/2016 121 Company 2 Division 2 Supplier A 3/15/2016 212 Company 2 Division 2 Supplier B 3/2/2016 323 Company 1 Division 3 Supplier B 3/31/2016 195 Company 2 Division 1 Supplier A 3/31/2016 127 Company 2 Division 2 Supplier A 3/31/2016 125 Company 2 Division 2 Supplier B 3/31/2016 545
• ###### 4. Re: Summing Revenue for a Month Based on a Point in Time Date

Hi Robert/Ivan:

There is one more solution along the lines that Ivan explained. We can put a new Calculated Field: month([Transaction date])-month([Calc. Start Date]. I have put that as Calculation2. Use that in the filter shelf as an attribute and set the value to '0'. Below is the screen shot:

Thanks

1 of 1 people found this helpful
• ###### 5. Re: Summing Revenue for a Month Based on a Point in Time Date

Hi PJ,

I like your solution of using a filter rather building the logic into a measure, definitely more elegant.  Since I'm a fan of Boolean I've modified it to MONTH([Valid From])=MONTH([Start Date]) and select True.  Same result of course.

Regards,

Ivan

• ###### 6. Re: Summing Revenue for a Month Based on a Point in Time Date

Agreed