Script logic error

[nicky_hays]

Hi

I want to calculate sales %. The formula for sales % is as follows.

(Sales of the period last Yr + Sales of the period year prior to last year) / (total sales last yr + total sales of yr prior to last year)

In the my category dimension, I am storing the Year with Category budget. So the %YR% will be 2016. I am getting the error: Syntax Error: Missing  name after .operator.

Also when I run this code through Data manager package, I get the following error:

"An exception with the type CX_SY_CONVERSION_OVERFLOW occurred, but was neither handled locally, nor declared in a RAISING clause

Overflow converting from '1915814392984806400' "

Please help.

Nicky

My code is as follows:

*SELECT(%YR%,"[YEAR]",CATEGORY,"[ID] = BUDGET")

*WHEN PROFIT_CENTER

  *IS BAS("PC_1000")

  *WHEN ACCOUNT

  *IS 990000

  *WHEN DATATYPE

  *IS Z_CURRENCY

  // BUDGET - Sales %

  *FOR %MYPER% = %YR%.01,%YR%.02,%YR%.03,%YR%.04,%YR%.05,%YR%.06,%YR%.07,%YR%.08,%YR%.09,%YR%.10,%YR%.11,%YR%.12

  *REC(EXPRESSION = (([TIME].[TMVL(-24,%MYPER%)] + [TIME].[TMVL(-12,%MYPER%)]) / ([TIME].[TMVL(-24,%YR%.01)] + [TIME].[TMVL(-24,%YR%.02)] + [TIME].[TMVL(-24,%YR%.03)] + [TIME].[TMVL(-24,%YR%.04)] + [TIME].[TMVL(-24,%YR%.05)] + [TIME].[TMVL(-24,%YR%.06)] + [TIME].[TMVL(-24,%YR%.07)] + [TIME].[TMVL(-24,%YR%.08)] + [TIME].[TMVL(-24,%YR%.09)] + [TIME].[TMVL(-24,%YR%.10)] + [TIME].[TMVL(-24,%YR%.11)] + [TIME].[TMVL(-24,%YR%.12)] + [TIME].[TMVL(-12,%YR%.01)] + [TIME].[TMVL(-12,%YR%.02)] + [TIME].[TMVL(-12,%YR%.03)] + [TIME].[TMVL(-12,%YR%.04)] + [TIME].[TMVL(-12,%YR%.05)] + [TIME].[TMVL(-12,%YR%.06)] + [TIME].[TMVL(-12,%YR%.07)] + [TIME].[TMVL(-12,%YR%.08)] + [TIME].[TMVL(-12,%YR%.09)] + [TIME].[TMVL(-12,%YR%.10)] + [TIME].[TMVL(-12,%YR%.11)] + [TIME].[TMVL(-12,%YR%.12)])) * 100, TIME = TMVL(0,%MYPER%), ACCOUNT = "SALES_PERCENT", DATATYPE = "Z_PERCENTAGE", CATEGORY = "BUDGET", AUDITTRAIL = "SALES_PER_ADJ")

  *NEXT

  *ENDWHEN

  *ENDWHEN

*ENDWHEN

[former_member186338 · Accepted solution]

Hi Nicky,

You have an incorrect idea in general - to calculate KPI using script logic and store it in some member. Please, read

And use dimension member formula for KPI calculation.

Vadim

[nicky_hays]

hi,

I also replaced the REC statement with the following:

*XDIM_MEMBERSET MEASURES = PERIODIC

*REC(EXPRESSION = ([TIME].[TMVL(-24,%MYPER%)] + [TIME].[TMVL(-12,%MYPER%)])/(([TIME].[TMVL(-24,%YR%.12)],[MEASURES].[YTD]) + ([TIME].[TMVL(-12,%YR%.12)],[MEASURES].[YTD])) , TIME = TMVL(0,%MYPER%), ACCOUNT = "SALES_PERCENT", DATATYPE = "Z_PERCENTAGE", CATEGORY = "BUDGET", AUDITTRAIL = "SALES_PER_ADJ")

But I get the error:

RUN_LOGIC:MDX statement error: System error in program CL_RSR_MDX_PERIODSTODATE and form FILL_MEMBER_TAB-03-