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
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)
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.
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
blom
as in 'jonge blom'
or
slang for the dutch word 'bloem' (= flower)
sorry :-) must have a sticky o key
It's not you or your keyboard. It happens on each and every english forum I participate in :o
Quote from: blom0344 on 12 Oct 2012 03:00:46 AM
blom
Hee hee. Cracks me up every time Ties :D :D :D You have the patience of a saint! :)
I feel your pain, no one spells my name right either :-(