Script logic to add YTD values

[Former Member]

Hi,

The below logic works fine for calculating Account ACCPYCALC(Periodic)=PYACCRUAL(Periodic)PYACCRELEASE(Periodic)PYACCACT(Periodic).

But we need to caculate Account ACCPYCALC(Periodic)=PYACCRUAL(YTD)PYACCRELEASE(YTD)PYACCACT(YTD).

Please provide your inputs.

-

---

*XDim_Memberset FBCategory=ACTUAL

*XDim_Memberset FBAccount=PYACCRUAL,PYACCRELEASE,PYACCACT

*XDim_Memberset FBEntityCostCenter=CEGB1129310Q01

*XDim_Memberset FBRptCurrency=GBP

*XDIM_REQUIRED=FBYEAR

*XDIM_REQUIRED=FBTIME

*WHEN FBACCOUNT

*IS PYACCRUAL,PYACCRELEASE,PYACCACT

*REC(FBACCOUNT=ACCPYCALC)

*ENDWHEN

*COMMIT

[Former Member · Accepted solution]

What if you built a 2 stage process to aggregate the YTD account values into seperate accounts using Business Rules (CalcAccount), then use script logic to add the 3 accounts to the final account. Wouldn't that work just as easily.

hope this helps.

[Former Member · Accepted solution]

Thanks Petar .... and that would be the final solution if nothing works out.

We were able to calculate using the below MDX. But we want to get rid of MDX and use SQL. Since it works fine on MDX, we are searching something similiar in SQL script.

#ACCPYCALC = ([FBACCOUNT].[PYACCRUAL], YTD().Item(0)) +SUM(YTD(),[FBACCOUNT].[PYACCRELEASE]) + SUM(YTD(), [FBACCOUNT].[PYACCACT])

[Former Member · Accepted solution]

Only one value is stored in Fact table as Signed data. YTD is calcualed at application level. SQL logic is run against SQL tables and MDX logic is run against the application cube. That is why we are able to pull YTD values in MDX logic and not in SQL logic. If you have any suggestions please add.