2 Replies Latest reply on Nov 8, 2019 3:58 PM by Kaz Shakir

    Running Sum Question

    Kyle Henes

      Hello,

       

      I am trying to identify the first date an ids running sum of spend for the monthly equals or exceeds the monthly budget. The view would be something like below: I cant get it to show without including all dates or all dates where the running sum of spend exceeded the monthly budget, i just want the first date that it exceeded the monthly budget. Thanks!

       

      ID 3331

      Date running sum of spend for month exceeded daily budget = 10/30/2019

      Spend = 732.34

      Monthly Budget = 729.0875

        • 1. Re: Running Sum Question
          madhuri.tanniru

          Please see if the following works

           

          The following gets you the dates when running_sum(Spend) exceeds Monthly budget

           

           

          the following returns min of the above returned dates per each Order

           

           

          Final

           

           

           

           

          Thanks

          Madhuri

          • 2. Re: Running Sum Question
            Kaz Shakir

            Kyle Henes,

            (second try to post)

            Here's my approach, and it's, perhaps, a bit unconventional.

             

            1. First join your datasource to itself, like so:

             

            This will give you all of the previous spending along side the current spending, and we will use this to calc the running sums.

             

            2. Create calculated fields to compute the running sums that we need:

             

            3. Create an indicator calculated field to determine when the spending exceeds the monthly budget:

             

            4. Create a calculated field to determine the first date when the spending exceeds the monthly budget:

             

            5. Place those items of a viz to see the results:

             

            6. And you could also create a chart to see the information in a different way:

             

            Hope that helps.  Please let us know if that solved your problem.

             

            Kaz.