How to manipulate 'integer'

[alex_]

Hi all,
I have a question.

Through stored procedure in DB I define each month to generate a 'month key'. Through it can count generated months. It is a format 'Integer'. I want to manipulate data from 'integer'. I am looking for feature that if you ask for example: (month key, -2) to return two months ago. If I ask (month key, -6) to return six months ago. This way can manipulate by months. Without doing it from the filter. Because I have one but it is level YTD.
For example has a similar function: tail([GAC Scorecard analysis)].[Months].[Months].[Month], 6). It returned six months ago for [Month]. But it does not work with 'Integer'.

Do you have an idea for a function or method that has similar functionality?

Thanks,
Alex

[blom0344]

Define a DB function , import it in the framework and then use it in a Cognos query subject. I think you are now trying to combine both SQL procedures and MDX type function at the front-end.
Alternatively , look at the Cognos _month_between function (depending on the type of model)

[bdbits]

Just a thought, but if you have a datatype of "date" instead of "integer", you have access to a bunch of built-in functions for date calculations. Even better is to create a time dimension, if that is an option open to you.

[wyconian]

HI

I agree with BDBITS and Bloom that you should try using the built in date functionality.  But if you can't I've added a relative month column to my calendar, basically it counts the number of months from the current date to every date in the calendar so the previous month = -1, 2 months ago = -2 current month = 0 and next month = 1 etc etc.

You could then get 6 months ago by filtering something like relative month <= -6

Good luck

[blom0344]

blom

as in 'jonge blom' 

or

slang for the dutch word 'bloem'  (= flower)

[wyconian]

sorry :-) must have a sticky o key

[blom0344]

It's not you or your keyboard. It happens on each and every english forum I participate in     :o

[MFGF]

blom

Hee hee. Cracks me up every time Ties :D :D :D  You have the patience of a saint! :)

[wyconian]

I feel your pain, no one spells my name right either :-(