4 Replies Latest reply on Nov 2, 2016 3:38 PM by Luis Vicentini

    Spread amounts over duration

    Bruce McCallum

      I have a budget that includes lines of spending (labour and cash) with start dates. I would like to create a visualisation that shows cash requirements for each week, month, or quarter. I have created a sample that has a number of data lines (source is Excel) of initiatives with different start dates, durations, and labour/cash amounts. The visualisation should show the actual cash requirement during that period, whether it is week, month, or day.

       

      How can I do this? Is it a calculated value? Do I use Running Total? I think I'm just missing something basic.

       

      Thanks in advance for any support.

       

      Regards, _Bruce

        • 1. Re: Spread amounts over duration
          Bill Lyons

          Well, this is not as simple as you thought it might be. But you probably figured that out, since your question remained unanswered for 2.5 weeks...

           

          The first challenge is that you need a date "scaffold," so that you can get values for cash requirements in increments that do not correspond to the lines in your data. So, I created a table in Excel, with all dates between 1/1/2016 and 9/26/2018 (a nice round number of 1000 days).

           

          The next challenge is joining that date scaffold with your data. The biggest problem here is that neither data blending nor a standard Excel table join allows a date join on >= or <=, which is what you need because of [Start Date] and [Finish Date].

           

          What I had to do is create Custom SQL. In order to do that against Excel, I had to use a "legacy connection." You can search for details about that, but it comes down to opening the Excel file by clicking the down-arrow on the Open button, and choosing "open with legacy connection." The Custom SQL is tricky too, since it is a little different than SQL Server. I settled on:

          select Date, [Start Date], [Finish Date], [Tactic], [Duration], [Labour], [Expense], [Total], [Hours]

              from [date scaffold$]

              left outer join [data$] on ([date scaffold$].Date >= [data$].[Start Date]

                  and

                  [date scaffold$].Date <= [data$].[Finish Date])

           

          The rest is more straightforward. I created a calculation of the number of days in each line (noting that the days are inclusive, so +1 is needed). Then dividing the total for the line by the number of days to get the daily amount for the line.

           

          So, using Date for the timeline on columns, and the sum of the amount per day for rows, we can get the amount of cash required at whatever frequency selected for Date. See the attached example for details.

           

          Does that help?

          1 of 1 people found this helpful
          • 2. Re: Spread amounts over duration
            Luis Vicentini

            Hey Bruce, Bill -

            Did you ever work through this? I'm working on a similar resource spread and would be interested to see your attached example, Bill, but it doesn't seem to be there. The idea of the scaffold and greater/lesser than join is great, though, thanks!

            Do you still have the example?

            best,

            Luis

            • 3. Re: Spread amounts over duration
              Bill Lyons

              That's interesting, I wonder what happened to the attachment. Perhaps I forgot to attach it. If so, I'm sorry to be 6 months late in doing so. Fortunately, I still had the file, and I have now attached it to my original answer. I hope that answers your questions.

              • 4. Re: Spread amounts over duration
                Luis Vicentini

                Thanks a lot Bill, that's great stuff.

                Cheers,

                Luis