1 2 Previous Next 28 Replies Latest reply on Jan 18, 2020 12:18 PM by Mary Beth Nolan

# Summing Aggregated to get Monthly Revenue

I'm trying to show the \$\$ of a multi year contract broken down over months under contract.  I could compute Total contract amount divided by days in contract then multiply that by the # of days in each month but the it would always look like Feb was less than Jan, etc.  So what I want is that if the month is a partial # of days under contract, ie contract starts Dec 15th for example then ends Dec 14th the following year then the # of partial days for the start of contract = 17 days and the last month of contract is 14 days.  All the other months are "full" or equal to however many days in that month.

I've successfully gotten the layout I want but the next step is causing me problems.  I want to get the sum of an aggregated field to use as denominator when I divide total contract amount/sum of aggregate.  In the example below it would be \$2,520/(.50+1+1+1+1+1+1+1+1+1+1+1+.50) = \$210.  Then I would take that \$210 and multiple by either the fraction of a month or 1.  (Please note that many of the other contracts when adding the partials plus the full months don't neatly add up to a whole number.  For example there might be a contract that starts on Dec 3 2015 and Ends on March 19, 2016)

This is output I'm looking for Company Onet:

105   210  210  210  210  210  210  210  210  210  210  210  105 Help would be greatly appreciated and hope this makes sense.

• ###### 1. Re: Summing Aggregated to get Monthly Revenue

Mary Beth,

I took a try at it.

I tend to work with Level of Detail calculations so that I can see how things are aggregating.

So I converted all your calculations into LODs.

Which particularly helped me get:

Total Contract Numerator:

{ FIXED [Account Name],[Date (Month / Year)]:AVG([Total Contract Amount])}

Total Contract Denominator:

{ FIXED [Account Name]:SUM([08. Fraction])}

Summing Aggregated to get Monthly Revenue • ###### 2. Re: Summing Aggregated to get Monthly Revenue

Hi Mary Beth,

I wanted to give it a shot using table calculations, too.

It took a bit of work to figure out what you were doing. It seems you essentially have assigned 0.5 as the Fraction for incomplete months. Is that correct? With that in mind, I reduced the whole thing to two calculations:

IF DATETRUNC("month",[Date])=DATETRUNC("month",[Contract Start Date])

OR DATETRUNC("month",[Date])=DATETRUNC("month",[Contract End Date])

THEN .5

ELSE 1

END

Monthly Amount:

WINDOW_MAX(MAX([Total Contract Amount]))/WINDOW_SUM(MAX([Fraction New]))*MAX([Fraction New])

- Computed using Month of Date Best,

Bryce | @BLarsenViz

• ###### 3. Re: Summing Aggregated to get Monthly Revenue

The "fractions" will vary if a month is partial, ie contract starts or ends anywhere that is not the first day of the month.  (to get fraction you have to go through a lot of calulations!)

Let me work with your improved LOD expressions.  Thank you for taking the time

• ###### 4. Re: Summing Aggregated to get Monthly Revenue

Yes, I agree LOD would be much better but my expertise is just not there yet.  I look forward to using your calculations to see if they work.  I'm sure they will!

• ###### 5. Re: Summing Aggregated to get Monthly Revenue

Hi Mary Beth,

Understood. So my calculation above is quite close then in two fields, right? We can add one more check to ensure the month is partial and keep it all in one calculated field to create the fraction.

I can send once in office, but it should be something like:

IF (DATETRUNC("month",[Date])=DATETRUNC("month",[Contract Start Date]) AND [Contract Start Date]<>DATETRUNC("month",[Contract Start Date]))

THEN .5

ELSE

END

• ###### 6. Re: Summing Aggregated to get Monthly Revenue

Hi Swaroop,

I think she has actually done that already and is from the DayPerMonth datasource she joined to.

After posting my most recent response, I realized we could get your answer without table calculations or LOD expressions.

First - get the length of contract:

DATEDIFF("month",[Contract Start Date],[Contract End Date])+1

Second - using similar approach previously provided, calculate the offset based on partial months:

IF [Contract Start Date]<>DATETRUNC("month", [Contract Start Date]) THEN 0.5 ELSE 0 END

+ IF [Contract End Date]<>DATEADD("day",-1,DATETRUNC("month", DATEADD("month",1,[Contract End Date]))) THEN .5 ELSE 0 END

Third - monthly payment:

[Total Contract Amount]

/

([Contract Months]-[Contract Months Partial])

Fourth - month fraction:

IF (DATETRUNC("month",[Date])=DATETRUNC("month",[Contract Start Date])

AND [Contract Start Date]<>DATETRUNC("month",[Contract Start Date]))

THEN .5

ELSE 1

END

Now you have everything you need as dimensions. Nothing fancy. Just multiply the Month FRactoin by the Monthly Payment and you're done! But same end result: I've numbered each calculation to make it easy. You can see it's all Dimensions. Best,

Bryce

1 of 1 people found this helpful
• ###### 7. Re: Summing Aggregated to get Monthly Revenue

Bryce,

I think the joined sheet was just the total number of days in a month, and not a scaffold of every day in the month.

But regardless, your latest workbook is a much, much better approach!

(I've deleted my scaffold suggestion because it was superfluous.)

I haven't been following the thread closely enough, so my apologies if this has already been addressed above,

but just wanted to add in the part about calculating the fractional month percentage based on the date

and the number of days in the month.

It adds a datediff of days:

IF [Contract Start Date]<>DATETRUNC("month", [Contract Start Date])  // doesn't start at beginning of the month
AND DATETRUNC("month", [Contract Start Date])=DATETRUNC("month",[Date])  //only add fractional part for beginning month

THEN // get the number of days from contract start date to the end of the month
// to get end of the month, add one month to contract start date month, and subtract one

ELSE 0 END

+
IF [Contract End Date]<>DATETRUNC("month", [Contract End Date])
AND DATETRUNC("month", [Contract End Date])=DATETRUNC("month",[Date])
THEN // get the number of days from beginning of the month to the contract end
DATEDIFF('day',DATETRUNC("month", [Contract End Date]),[Contract End Date])/[Day Per Month]
ELSE 0 END

• ###### 8. Re: Summing Aggregated to get Monthly Revenue

Thank you all for your help and I look forward to working with your solutions.  Regarding data scaffolding - that was the first step I did.  I exported all the needed fields from Salesforce to excel.  I also created an excel file called dayofmonth.xlsx which had every date from 1/1/2012 - 12/31/2022 and the # of days in each of those months.

I then opened a new Tableau file and used those two excel files and joined as:  contract start date <= date and contract end date >=date.

I then build this tab called COHORT.  For example here is a display for 6 customers  where the first figure is saying "is this month's revenue the SAME, UP, DOWN, NEW or LAPSED from prior month".  Second figure is the monthly revenue (which you helped with).  Third figure is # of consecutive years of spend and Fourth is the first year they did any spend. I then have to export that worksheet to excel and create yet ANOTHER Tableau workbook using that file be able to computer Churn Rate, Customer Lifetimevalue, and other visualizations; such as:  So with your help I've at least reduced one of the exports to excel but I'm still having to export twice.

Once I get home from work today I look forward to really trying out your additional solutions!

• ###### 10. Re: Summing Aggregated to get Monthly Revenue

FWIW: I think you can still do this without any LOD expressions. Just need to modify how the fraction is calculated in the example above (seems I misunderstood how you wanted that calculated). I just wanted to mention as any time you can avoid LOD the better as you'll get much better performance.

Best of luck!

Bryce

• ###### 11. Re: Summing Aggregated to get Monthly Revenue

For some reason, I'm unable to duplicate with my full data set your first workbook with the LOD's.

I'm starting over and wrapping my brain around problem.  Attached are two examples of correct output if I were using Excel.  Everything is pretty much a formula except those darn partial days!

• ###### 12. Re: Summing Aggregated to get Monthly Revenue

For some reason, I'm unable to duplicate with my full data set your first workbook with the LOD's.

I'm starting over and wrapping my brain around problem.  Attached are two examples of correct output if I were using Excel.  Everything is pretty much a formula except those darn partial days!

• ###### 13. Re: Summing Aggregated to get Monthly Revenue

For some reason I don't think my attachment was with email reply

• ###### 14. Re: Summing Aggregated to get Monthly Revenue

For some reason I can't add my attachement.  Here is a screen shot - you can probably follow logic.  The output in yellow is what I'm looking for. 1 2 Previous Next