1 Reply Latest reply on Apr 12, 2013 11:34 AM by Dan Benevento

    How do I sum quantities on a relative 'last day of week' using Table Calcs?

    Dan Benevento

      First time poster, long time user… You forum wizards are awesome!

       

      Here’s the issue I’m having: I’m working with a Part Supply / Demand data set that calculates Ending Balance Quantity on the database side.

      Part ID    PartName    NeedDate    Ending Balance Quantity

      1    Part A    1/1/2013    5

      1    Part A    1/4/2013    10

      1    Part A    1/7/2013    -5

      2    Part B    1/5/2013    35

      2    Part B    1/1/2013    50

      2    Part B    1/6/2013    60

      3    Part C    1/9/2013    15

      3    Part C    1/10/2013    5

      3    Part C    1/11/2013    5

      I have a worksheet that calculates what the Ending Balance Quantity is on the last day of the week for a given part. This works great using this Table Calc:

      If LAST()=0 THEN SUM([EndingBalanceQuantity]) ELSE NULL END

      Then I set the Table Calc to compute using NeedDate at the deepest level, restarting every Month of NeedDate.

       

      What I can’t figure out how to do is find the right table calculation to sum ALL of the Ending Balance Quantities for ALL parts for the Last day of the week that that part had an Ending Balance Quantity.

       

      So if Part A had an Ending Balance of 10 on 1/4/2013, and Part B had an Ending Balance of 35 on 1/5/2013, how can I create a Table Calc that returns 45 for week 1 of January?

       

      Sample workbook attached.

       

      Thanks!

      Dan Benevento

        • 1. Re: How do I sum quantities on a relative 'last day of week' using Table Calcs?
          Dan Benevento

          As an FYI for anyone searching this archive.  Andy Rizzuto from Clarity Solution Group helped me work our a solution to this problem.  We used a nested table calc that looks like this: 

           

          Ending Balance Calculation on Last Day

          If last()=0 then sum([EndingBalanceAmtValue]) Else null End

           

          Ending Balance Calculation on Last Day for All Parts:

          IF FIRST()==0 THEN window_sum([Ending Balance Value on Last Day], 0 , IIF(FIRST()==0,LAST(),0))END

           

          Then you pull out Ending Balance Calculation on Last Day for All Parts and edit the calculation to address Ending Balance Calculation on Last Day on "Day of NeedDate" and address Ending Balance Calculation on Last Day for All Parts on Week of NeedDate, PartID, and Day of NeedDate restarting every Week of NeedDate. 

           

          The point of this nested calc is to first establish what each parts ending balance is on the last day for which that part =had an ending balance...Then to sum up all of these ending balances for all parts.  The FIRST/LAST logic in the second calculation is to get rid of the overlapping text. 

           

          Hope this makes sense... Feel free to reach out if you run into a similar problem and need clearer instructions. 

           

          Cheers,

          Dan