
1. Re: How do I sum quantities on a relative 'last day of week' using Table Calcs?
Dan Benevento Apr 12, 2013 11:34 AM (in response to 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