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

    Mary Beth Nolan

      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
          swaroop.gantela

          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])}

           

          Please see workbook v2019.4 attached in the Forum Thread:

          Summing Aggregated to get Monthly Revenue

           

          327652churn.png

          • 2. Re: Summing Aggregated to get Monthly Revenue
            Bryce Larsen

            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

             

             

            Does this match your goal?

             

            Best,

            Bryce | @BLarsenViz

            • 3. Re: Summing Aggregated to get Monthly Revenue
              Mary Beth Nolan

              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
                Mary Beth Nolan

                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
                  Bryce Larsen

                  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]))

                     OR (DATETRUNC("month",[Date])=DATETRUNC("month",[Contract End Date]) AND [Contract End Date]<>DATEADD("day", -1,DATETRUNC("month",DATEADD("month",1,[Contract End Date]))))

                  THEN .5

                  ELSE 

                  END

                  • 6. Re: Summing Aggregated to get Monthly Revenue
                    Bryce Larsen

                    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]))

                      OR (DATETRUNC("month",[Date])=DATETRUNC("month",[Contract End Date]) AND [Contract End Date]<>DATEADD("day", -1,DATETRUNC("month",DATEADD("month",1,[Contract End 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
                      swaroop.gantela

                      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

                      DATEDIFF('day',[Contract Start Date],DATEADD('day',-1,DATEADD('month',1,DATETRUNC("month", [Contract Start Date]))))/[Day Per Month]
                      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
                        Mary Beth Nolan

                        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!

                        • 9. Re: Summing Aggregated to get Monthly Revenue
                          Mary Beth Nolan

                          Your first solution is returning the answer I want - actually returning the fraction * avg monthly amount  This is from

                           

                           

                           

                          The latest work is not correct.  Notice the first and last month are the same $4,097.  When the first month should return $4493 and the last month should return $3700.

                           

                           

                           

                           

                          So it looks like LOD have to be in the analysis. 

                          • 10. Re: Summing Aggregated to get Monthly Revenue
                            Bryce Larsen

                            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
                              Mary Beth Nolan

                              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!

                               

                              Didn't know if this would help you understand the desired output.

                              • 12. Re: Summing Aggregated to get Monthly Revenue
                                Mary Beth Nolan

                                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!

                                 

                                Didn't know if this would help you understand the desired output.

                                • 13. Re: Summing Aggregated to get Monthly Revenue
                                  Mary Beth Nolan

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

                                  • 14. Re: Summing Aggregated to get Monthly Revenue
                                    Mary Beth Nolan

                                    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