epicor version 9.05.701
How can i write a calculated field to give me:
year to date monthly movement.
year to date budget amount
year to date variance
(so it would need to be accumulated for each period but starting again at a new fiscal year)
Thank you.
I don’t know how to do it in a calculated field. I would make the baq updatable and calc that in the getlist with abl code. We do something like this in a monthly sql query that stores the data in a UD table since the calculation is set per month.
The TOTAL() (or is it SUM()?), has the ability to specify the scope as a parameter. You might need to make a calc field to use as that parameter.
If that doesn’t work, make a subquery to sum, and group by
The first suggestion uses OVER partition)
`SUM(ICount) OVER (PARTITION BY BrandId )
Hi,
It is TOTAL() but im not sure how to write at each change in GL account and fiscal year AND then accumulate up through the periods.
Are you looking for a running total like:
FY FP GL ACCT TRAN_AMT FP_TOTAL FY TOT
2018 01 01-2345 100.00 100.00 100.00
2018 01 01-2345 200.00 300.00 300.00
2018 01 01-2345 300.00 600.00 600.00
2018 02 01-2345 1000.00 1000.00 1600.00
2018 02 01-2345 2000.00 3000.00 3600.00
2018 02 01-2345 3000.00 6000.00 6600.00
2018 01 99-9876 100.00 100.00 100.00
2018 01 99-9876 200.00 300.00 300.00
2018 01 99-9867 300.00 600.00 600.00
Use
FP_TOTAL = SUM(TRAN_AMT) OVER(ORDER BY FY, FP, GL_ACCT)
FY_TOTAL = SUM(TRAN_AMT) OVER(ORDER BY FY, GL_ACCT)
Or will a “group’s total” on each line do?
FY FP GL ACCT TRAN_AMT FP_TOTAL FY TOT
2018 01 01-2345 100.00 600.00 6600.00
2018 01 01-2345 200.00 600.00 6600.00
2018 01 01-2345 300.00 600.00 6600.00
2018 02 01-2345 1000.00 6000.00 6600.00
2018 02 01-2345 2000.00 6000.00 6600.00
2018 02 01-2345 3000.00 6000.00 6600.00
2018 01 99-9876 100.00 600.00 600.00
2018 01 99-9876 200.00 600.00 600.00
2018 01 99-9867 300.00 600.00 600.00
Use
FP_TOTAL = SUM(TRAN_AMT) OVER(PARTITION BY FY, FP, GL_ACCT)
FY_TOTAL = SUM(TRAN_AMT) OVER(PARTITION BY FY, GL_ACCT)
Note that the two total columns have the same values for “groups”
EDIT: I added examples for the calc fields below each. Note, I’ haven’t fully tested, so my syntax might be askew…
Hi,
A running total would work but reset at each change in GL account AND fiscal year…then it must accumulate up in the right periods…so movement of P1 + movement of P2 + movement of P3 etc…
Use something like:
SUM (TranAmt) OVER (ORDER BY FiscYr, FiscPer, GL_Acct)
Those params after the ORDER BY would be your fields (can be calculated ones)
Thank you, i’ll give this a try and let you know.
Im in the UK so it will be first thing tomorrow now. 
First off … I didn’t notice that you were using E9 (I should have known when you were using TOTAL and not SUM). So this approach may not work for you.
Make another BAQ, just to test if the OVER(...) will work in E9
@Rowley150 i’ve been waiting for you to upgrade to 10 since 2015… i am still waiting come join the fun club 
I think in E9 isn’t there something where you can right click on the Node and mark the table as a “Total/SUM” Table. (You right click on the green node in Designer).
The only way I always knew how to do it in E9 was to make an Updateable BAQ and use the BPM Editor in the Update Tab and add a BPM on the GetRows and then set Calculated_Fields via BPM on UBAQ.
It isn’t my call! 
It needs sign off from directors etc.
In V8, I think there was a checkbox in the list of tables selected.
The faster he upgrades, the sooner I can retire my E9 VIrtual Machine, I keep around just for @Rowley150
I dont think I ever used this, feature - I am sure theres something in the E9 Help Files… But see if you can find/figure out how this works:
EDIT: It looks like you already know how to use this feature well, perhaps it doesnt work for what you intend to do with it…
Therefore, I think you might just end up with updateable BAQ, despite it not being used to update anything, you can then put BPMs on it.
Calculated field syntax:
TOTAL (GLPeriodBal1.BalanceAmt) OVER (ORDER BY GLAccount.GLAccount, GLPeriodBal1.FiscalYear, GLPeriodBal1.FiscalPeriod)
summary table added:
exported to excel, here is what is happening:
columns B to J is what is exported from the BAQ…
column K is what i’ve done to work out what it should be…
column J seems to be doing something odd so i think the syntax in the calculated field needs a bit of tweaking.
Whoops … misread that [ original post’s text is deleted]
Try removing GLPeriodBal1.FiscalPeriod from the OrderBy
And what function does the added Summary table provide?
still the same answer when taking out the glperiodbal1.fiscalperiod from the order by.
i added the new glperiodbal summary table because the baq wouldn’t work without it, it returned an error.
Do any of the columns come from that summary table? And what are the relationships to GLAcct?
What happens if you remove that summary table, and change the GLPeriodBal table to summary?
