on 12-14-2012 11:02 AM
By default Calendar Year/Month field is not getting sorted, I have tried creating few formula variables but I couldn't make it. I am using this object in graph also so it affects that too.
For example:
Calendar Year/Month has values 10/2011 11/2011 12/2011 01/2012 02/2012 .......10/2012 11/2012 12/2012 but when I include this object in WebI it shows like this 01/2012 02/2012 ..................10/2011 10/2012 11/2011 11/2012 12/2011 12/2012. Drawing graph was also wrong as the data marked in the sequence mentioned.
Calendar Year/Month is in character data type which is causing this issue i.e not able to sort
Can anyone suggest me to overcome this ..
Hi Gokul,
First Convert this char Data type Oject to Date by using Todate( ) ,this will resolve your issue.
eg : var_CalMonth= ToDate([Calendar Year/Month];"MM/dd/yyyy")
and then you can display the date to the format you want using FormatDate( )
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Kiruthika,
I have used this formula
=ToDate([L01 Calendar Year/Month Key];"MM/yyyy")
it gave me 10/1/08 for 10/2008, I have checked the format it was in date
Then I used formatdate() function to make it 10/2008, it turned correctly but still the datatype is string
can you suggest me what could be the problem ?
Below thread may also help you
Changing the Datatype at Bex Level is not advisable , as this may affect other reports which is using the same object.
You can try an alternative solution by creating a User Defined Dimension at universe level with the Cal month object with Datatype as Date and try using this User Define Object at your report.
Is your Bex Query Based on Infocube or DSO? Check whether the Calmonth object is coming as Char or as Date from the backend.
Try using the Detail Object Date Key in the User defined Dimension created in the universe as, we have tried this solution when we face the same issue and it worked perfectly.
Hi,
I got a much easier solution. I include Year in the report. First sort by year, then by month/year. After, if you don't want to display Year, just hide it, and done
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
80 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.