YTD calculation using SQL script logic

[Former Member]

Hi,

Has anyone used the SQL logic to generate YTD numbers. I have a requirement where I need to have a account which should have a Net_Income_YTD value stored in it. I can have this easily through MDX, but trying to see if we can achieve this in any way through SQL logic.

MDX formula for this calculation is:-

(Account).(Net_Income_YTD) = (Account).(Net_Income),(Measures).(YTD)

Thanks

Sharath

[Former Member]

Hi,

The only way that i can see that you would be able to calculate the YTD value is to calculate it before you need to use it. You could either calculate the value in the same script by detecting the YEAR and calculating the YTD value for every month for the year and then using the appropriate value in your next step. Alternatively you could also write this into your default logic and calculate the value every time the value change.

The formula would be something like (please forgive the syntax, i'm just winging it a bit here )

*calc_each_Period
*when time.monthnum
*is <> 1
*REC(Expression=get(account="income",time=prior)+get(account="income"),NOADD,account="YTDincome")
*else
*REC(Expression=get(account="income"),NOADD,account="YTDincome")
*endwhen

Regards,

Andries

[Former Member]

Andries,

This is a good idea and I had thought of the same logic but found it difficult to make this work. Not sure whether I am making any mistakes in the script in replicate this idea or is it a limitation of the script. I found different issues in making this work correctly.

Now when I used the formula which you have given I see a new problem.

My "income" account itself is a calculated account which is derived using SQL logic. But when I have your formula added to my Default.lgx file neither "income" nor "YTDincome" is getting generated in my WB table. Looks strange as "income" formula comes before "YTDincome" formula and it is not dependent on "YTDincome".

Have you actually used this in any of your application and able to use it without any errors? Please advice.

Thanks

Sharath

[Former Member]

Hi,

You are going to struggle using the calculated account with SQL logic, but there may be a way. I take it that the "income" account is the income statement roll up account that is the sum of all the income statement accounts. If this is the case, then you can put a member property on the income statement accounts that will identify them. You can then first do the summing:

*when Account.FINSTMNT
*is "IS"
*REC(factor=1,Account="SUMINCOME")
*ENDWHEN
*GO

*when time.monthnum
*is NOT 1
*REC(Expression=get(account="SUMINCOME",time=prior)+get(account="SUMINCOME"),NOADD,account="YTDincome")
*else
*REC(Expression=get(account="SUMINCOME"),NOADD,account="YTDincome")
*endwhen

I'm sure this will effectively do a sum of all the income statement account (giving you the same value) and then get a YTD calue thereafter.

Regards,

Andries

[Former Member]

Hi Andries,

I used the below syntax as per your explaination but no records are getting generated in WB table for the YTD account. Can you please review the below syntax and advice whether I need to change anything.

PS: for testing I am sending data to NI account and expecting YTD values in NI_YTD

*XDIM_MEMBERSET ACCOUNT=<ALL>

*CALC_EACH_PERIOD

*WHEN TIME.MONTHNUM

*IS NOT 1

*REC(Expression=get(account=NI,Time=Prior)+get(account=NI),NOADD,Account=NI_YTD)

*Else

*REC(Expression=get(account=NI),NOADD,Account=NI_YTD)

*ENDWHEN

*COMMIT

Thanks

Sharath

[Former Member]

Andries,

I used the below syntax available on the SAP 7.0 admin guide and I think only this works correctly without any errors.

//----

---

Net Income to BS

*CALC_EACH_PERIOD

*XDIM_MEMBERSET TIME=PRIOR,%TIME_SET%,%YEAR%.DEC

*CALC_DUMMY_ORG ACCOUNT=PARENTH1

*WHEN TIME

*IS PRIOR

*WHEN ACCOUNT

*IS CYNI

*REC(ACCOUNT=#OPE_CYNI,TIME=NEXT)

*ENDWHEN

*ELSE

*WHEN ACCOUNT

*IS #NETINCOME

*REC(ACCOUNT=#OPE_CYNI,TIME=NEXT)

*REC(ACCOUNT=CYNI)

*IS #OPE_CYNI

*WHEN TIME.PERIOD

*IS<>JAN

*REC(ACCOUNT=#OPE_CYNI,TIME=NEXT)

*REC(ACCOUNT=CYNI)

*ENDWHEN

*ENDWHEN

*ENDWHEN

//----

---