Running Total in Script

[pkelly]

I am pulling information in from our trading system and want to create a field which equates to running total of budgeted days.

In the first period of the financial year, this will equal the first period thereafter it will start to accumulate until a new financial year is met.

From previous posts, I have come up with the routine below....

I am not getting the cumulative figure only the working days for the first period in the year.

Monthly:
LOAD *,
IF("cam_FinancialYear" = Previous("cam_FinancialYear"), ("cam_BudgetedDays" + Peek('cam_BudgetedDaysCumulative',-1,'Monthly')), cam_BudgetedDays") AS cam_BudgetedDaysCumulative;

LOAD
UPPER(Code) AS %AnalysisPeriodKey,
UPPER(Code) AS cam_AnalysisPeriod,
"Code-Desc" AS cam_MonthYear,
value1 AS cam_FinancialYear,
Value2 AS cam_MonthNumber,
Value3 AS cam_BudgetedDays,

sequence AS cam_YearQuarter,
IF(active = 0, 'No', 'Yes') AS cam_Active;
SQL SELECT *
FROM PUB."cde_codes"
WHERE Prefix = 'ZFINPERIOD'
AND Code >='$(varFromAnalysisPeriod)';

Any ideas?

[pkelly]

Now I am totally confused...

I changed my script by adding the following line after the cam_BudgetedDaysCumulative statement (was trying a suggestion from a different post keeping my original intact)..

If("cam_FinancialYear" = Previous("cam_FinancialYear"), RangeSum(cam_BudgetedDays, Peek('cam_BudgetedDays')),cam_BudgetedDays) AS Cumulative;

My cam_BudgetedDaysCumulative column is now calculating properly?

The "Cumulative" field result is this month and previous month added together.

Why does the introduction of this new field cause the cam_BudgetedDaysCumulative to work?

Regards

Paul

[pkelly · Accepted solution]

Got it...

IF("cam_FinancialYear" = Previous("cam_FinancialYear"), ("cam_BudgetedDays" + Peek('cam_BudgetedDaysCumulative')), "cam_BudgetedDays") AS cam_BudgetedDaysCumulative,