How to get the nuber of periods(months) for a given date range

[ravindravoggu]

Hi All,

I am using dimensional model and in the prompt page I have two value prompts from date - to date(range)
ex: Jan 08, Feb 08,.... so on. I need to dertermine number of months between the selected from date - to date. if i select Jan 08 for from date and Aug 08 for to date then the return value should come 8(numeric value). To myn knowledge we have to use dimensional functions please let me know any one can help me on this?

Thanks in Advance.

Ravi.

[cschnu]

Look at the documenation for
_months_between ( date_exp1, date_exp2 )
Returns a positive or negative integer number representing the number of months between date_exp1 to date_exp2. If date_exp1 < date_exp2, then a negative number is returned.

[ravindravoggu]

Hi,
I tried this but it was not working because, right now date format is Jan 09, Feb 09 ...... .If you have date format like '2009-01-01' it will work. We can't convert this becaus it is coming from the cube. So I am looking for alternative.

Thanks,
Ravindra.

[billylodz]

convert to date first

[tiga123]

I don't know exactly the function which you are searching, but what you can also do is

add a measure to your datasource which inserts a 1 on the first record of the month.
then insert this measure into your cube. if you create a filter in you report, the measure will sum within your selected periods.

this functionality is working like a categorie count.

[imts]

U can do it. A bit wierd :) But Is works !

Append 01 to Jan-09 and cast to date then use months_between function

Eg;

mydate = Jan-09

months_between ( cast( ( '01-' || mydate1 ) , date ) , cast( ( '01-' || mydate2 ) , date ) )