cancel
Showing results for 
Search instead for 
Did you mean: 

Simplify logic script

former_member591760
Participant
0 Kudos

I want to update an existing package by adding a new section to take the actual dollars from a 12 month period and copy the total to the month and category selected when the package is run.

Right now I am scoping the time by listing each period like so:

*XDIM_MEMBERSET TIME = period1,period2,period3,...period12

Then I am basically repeating the same steps in the WHEN block:

*WHEN TIME
  *IS "period1","period2","period3",..."period12"
    *REC(EXPRESSION=%VALUE%,TIME=%TIME_SET%,CATEGORY="%CATEGORY_SET%")
*ENDWHEN

This works OK but requires someone to manually update the periods each time we run the package.  What I would like to do is rewrite the logic so it does not require the manual update each time the package is run.

Is it possible to use a SELECT statement to determine my 12 month period?  It would be the month selected when the package is run, plus the 11 months prior to that.  For example, if the user selected MAR2014, it would be the 12 month period from APR2013 - MAR2014.

I imagine it would look something like below but for the SELECT statement but I don't know how to get the other 11 months.

*SELECT(%TWELVEMONTHS%, "[ID"], "TIME", "[ID] = '%TIME_SET%' AND ????")

Once I have the variable populated, would the syntax below work for the WHEN block?

*XDIM_MEMBERSET TIME = %TWELVEMONTHS%

*WHEN TIME
  *IS "%TWELVEMONTHS%"
    *REC(EXPRESSION=%VALUE%,TIME=%TIME_SET%,CATEGORY="%CATEGORY_SET%")
*ENDWHEN

Regards

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

I have used this in the past

*SELECT(%CURR_YR%,[YEAR],"TIME","ID='%TIME_SET%'")

*SELECT(%ALL_MTH%,[ID],"TIME","LEVEL='MONTH' AND YEAR='%CURR_YR'")

%ALL_MTH% should contain the months of the current year.

BR,

Arnold

former_member591760
Participant
0 Kudos


Hi Arnold,

This helps a lot thank you.  But for this package the months overlap over two different years.  I would need to get the first six months of the year, and the last six months of the previous year.  Can I use something like below for the six months in the current year?

*SELECT(%CURR_YR%,[YEAR],"TIME","ID='%TIME_SET%'")

*SELECT(%ALL_MTH%,[ID],"TIME","LEVEL='MONTH' AND YEAR='%CURR_YR%' AND MONTHNUM <= 6")

And then is it possible to do something similar and say CURR_YR -1 to get months 7-12 from the previous year?

Best regards.

Former Member
0 Kudos

Hi,

I haven't tried that but I would think it should be. Maybe if you have a column that gives the prior year for each period then you could use that.

BR,

Arnold

Answers (0)