on 04-24-2014 9:27 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
15 | |
3 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.