how to convert Mmm-yyyy to mm-yyyy

system · February 20, 2009, 4:26am

Hi,
We have a variable in Format Apr-2008 I want to convert it into 04-2008 . So that sorting could be done .

How to do it , all the functions available work on full date , i am not getting any function for this .

Thanks
Vishal

vishu780 (BOB member since 2008-12-04)

system · February 20, 2009, 4:36am

Hi,

Try a formula like this:

=FormatDate(ToDate("01-" &amp; [date variable];"dd-Mmm-yyyy"); "mm-yyyy")

Marek Chladny (BOB member since 2003-11-27)

system · February 20, 2009, 4:43am

Hi,
thanks for the reply i tried your way but it is giving error , may be because our date variable is in the Mmm-yyyy format , and not in complete date format.

Can you please suggest some workaround.

Thanks
Vishal

vishu780 (BOB member since 2008-12-04)

system · February 20, 2009, 4:50am

When you said the formula gave you an error you should post the exact formula you used and the exact error message you got

Marek Chladny (BOB member since 2003-11-27)

system · February 23, 2009, 4:43am

Hi ,
Thanks for all your responses, it is working now but one problem ,it is sorting correctly for one year but in case it is dec-2007 and jan-2008 then jan-2008 is coming first.

How to fix this .This is the formula i am using right now.
=FormatDate(ToDate([Short Month Year];“Mmm-yyyy”);“MM-yyyy”)

Thanks
Vishal

vishu780 (BOB member since 2008-12-04)

system · February 23, 2009, 4:46am

Then use a formula like this:

=FormatDate(ToDate([Short Month Year];"Mmm-yyyy");"yyyy-MM")

Marek Chladny (BOB member since 2003-11-27)

system · February 23, 2009, 7:07am

The reason of wrong sorting is your variable is string type, formatdate function will return string value. You just need to change it to date, somthing like this:
=ToDate(FormatDate(ToDate([Short Month Year];“Mmm-yyyy”);“MM-yyyy”);“MM-yyyy”)
Infact if your your universe object is already in Mmm-yyyy format (Date type) you can use below variable:
=ToDate(FormatDate([Short Month Year];“MM-yyyy”);“MM-yyyy”), this one should work perfectly.

Prashant Purohit (BOB member since 2009-02-18)

system · February 23, 2009, 1:43pm

Why convert it at all? Why not just format the output on the report to look exactly as you want? That way it will sort as a date anyway.

debbie

Debbie (BOB member since 2005-03-01)

system · February 24, 2009, 6:00am

I want to sort the date object in the order the following order
Ex:
Jan 08
Jan09
Feb 08
Feb09
Mar 08
Mar09
And I dont want to use the CUSTOM SORT option for this.

SRR (BOB member since 2009-01-20)

system · February 24, 2009, 9:08am

Oh, I see what you mean.

Basically if you date-sort, you’ll get
jan 08, feb 08, mar 08, jan 09, feb 09, mar 09

If you text-sort, you’ll get
feb 08, feb 09, jan 08, jan 09, mar 08, mar 09

So you might be better off splitting into two variables - a month and a year separately. have them as dates and sort by month, then year. That should work.

Debbie

Debbie (BOB member since 2005-03-01)

system · February 24, 2009, 12:05pm

Actually I want that variable as the x-axis of a bar graph… So can u please tell me wat wud work??

SRR (BOB member since 2009-01-20)

system · February 24, 2009, 1:00pm

OK - you need to split your date into two separate entities.

At universe level, assuming your database is oracle:

year=to_number(to_char(date_variable,‘SYYYY’))
month=to_number(to_char(date_variable,‘MM’))

You can get Bob to do this automatically for you in Deisgner by clicking the Automatic Time Hierarchy button on the object’s properties. This is the easiest way to do it, but if you can’t do it in Designer, create report-level variables instead.

At report level, in webi:

month=MonthNumberOfyear(date_variable)
year=Year(date_variable)

Now you’ll have month and year as numbers. Use both of these on your x axis and sort by month and then year.

Debbie

Debbie (BOB member since 2005-03-01)

system · February 24, 2009, 1:05pm

You can check this. The second last post is exact the opposite of your requirement.

aniketp (BOB member since 2007-10-05)

system · February 24, 2009, 1:06pm

Thanks I will have to try this… I will let u know the result…

SRR (BOB member since 2009-01-20)

system · February 24, 2009, 1:11pm

Sandhya, I am sorry to say that I think it won’t help you. As I thought of giving it as an alternative to Vishu.

aniketp (BOB member since 2007-10-05)

system · February 24, 2009, 1:16pm

ok fine… Now tell me that if i write free-hand sql for this object (in a separate dateprovider) or in the present dataprovider it self i write the code for the required date format will it work?

SRR (BOB member since 2009-01-20)

system · February 24, 2009, 1:20pm

Check the option: “Do not Generate SQL”. And then modify the query as per your requirement. It will definately work. But there is a disadvantage of this solution: Whenever you will add or delete any object, the query will automatically be modified.

Also if possible for you, can you help us with the screenshot of your issue…

aniketp (BOB member since 2007-10-05)

system · February 24, 2009, 1:25pm

Ya thats the reason I was hesitating to use that solution…

SRR (BOB member since 2009-01-20)

system · February 24, 2009, 1:30pm

You’ve lost me now - which solution are you trying to implement? If it was my one - just create webi variables…

Debbie

Debbie (BOB member since 2005-03-01)

system · February 24, 2009, 1:57pm

Ya Debbie I will try that one(Automatic Time Hierarchy) but the other solution of Free hand SQL has its own disadvantages…

SRR (BOB member since 2009-01-20)