5 Replies Latest reply on Feb 19, 2016 2:01 PM by Dan Sanchez

    Calc Field: Summation with Upper and Lower Bound

    Erin Gehn

      Hello~

      I am trying to sum items from one point in the table to another where the lower bound is 1 point away and the upper bound is #_of_months away. I am using a lookup function.

       

      If you believe there is a more efficient way of doing this, I welcome those comments as well.

       

      Please see attached. The function in question is "Month Written Running Total"

      This is basically what I would like to do::

       

      IF attr([Customer Name]) = lookup(attr([Customer Name]),1) then

      ([Month Written]) + LOOKUP(([Month Written]),1) + lookup([Month Written],2) +LOOKUP([Month Written],3)+...
      + LOOKUP([Month Written],MONTH(Order Date Para)) END

       

      In other words, I'd like to sum "Month Written" (which is already a calculated field within the table) for a given Customer name from the bounds of the OrderDatePara month to the OrderDatePara month of the previous year.

       

      Does this make sense? Are there any details I can disclose that would aid in understanding my issue?

        • 1. Re: Calc Field: Summation with Upper and Lower Bound
          Dan Sanchez

          Hi Erin!

           

          Hopefully I didn't misunderstand what we're going for but I think I've got a possible solution.

           

          First off, I re-wrote the Month Written to simplify the formula a bit:

           

          [Month Written_NEW]:

          SUM([No Pro]) - LOOKUP(SUM([No Pro]),1)

           

          We don't need to do the IF/THEN clauses because we can just set the scope of the table calculation to only compute within each distinct customer.  I've done this by editing the advanced Compute Using settings (right-click field in the viz > Edit Table Calculation > Advanced drop down in Compute Using).

           

          Next, I updated the running total formula to use the following:

           

          [Month Writting Running Total_NEW]:

          IF MIN([Order Date]) >= DATEADD('year', -1, [Order Date Para])

          AND MIN([Order Date]) <= [Order Date Para]

              THEN WINDOW_SUM([Month Written_NEW])

          END

           

          And we'll then end up with something like this:

           

          2-8-2016 7-19-04 AM.png

           

          Let me know if this is what you were looking for.

           

          Thanks Erin!

          1 of 1 people found this helpful
          • 2. Re: Calc Field: Summation with Upper and Lower Bound
            Erin Gehn

            This was super helpful!!

            Is there a way to only sum the monthly written new for those date criteria though?

            In other words, you got the window sum to display if it exists between two date items (the parameter and order date) and this works perfectly, but the window sum is taking the sum of all dates to a customer name...not just the ones that fit the date parameter/order date criteria.

             

            Does this make sense?

            THANK YOU!!!

            • 3. Re: Calc Field: Summation with Upper and Lower Bound
              Dan Sanchez

              Hi Erin!

               

              That makes total sense!  I think we just need to modify the formula slightly to something like this:

               

              IF MIN([Order Date]) >= DATEADD('year', -1, [Order Date Para])

              AND MIN([Order Date]) <= [Order Date Para]

              THEN

              WINDOW_SUM(

              IF MIN([Order Date]) >= DATEADD('year', -1, [Order Date Para])

              AND MIN([Order Date]) <= [Order Date Para]

                  THEN [Month Written_NEW]

              END)

              END

               

              first we change the position of the WINDOW_SUM to wrap around the IF/THEN statement, then we add the second IF/THEN at the beginning to only display the marks for the selected date range.  We should end up with something like this:

               

              2-9-2016 11-17-44 AM.png

               

              Let me know how that looks.  Thanks!

              1 of 1 people found this helpful
              • 4. Re: Calc Field: Summation with Upper and Lower Bound
                Erin Gehn

                Your proposed solution appears to be logical. And super super close.

                -2-20+30-105+95+8+3 = 9 not 7

                Any idea where those other 2 are?

                • 5. Re: Calc Field: Summation with Upper and Lower Bound
                  Dan Sanchez

                  Hi Erin!

                   

                  Took me a little bit of fiddling with the view but I think I figured it out.  It looks like we're running into a rounding issue here.  If we format the Measure Value field and show the decimal places we get a more accurate representation of the data.

                  2-19-2016 2-00-54 PM.png

                   

                  Hopefully that clears things up

                   

                  Thanks!

                  1 of 1 people found this helpful