changing a custom date column to number

TKQ5Q

hi all

1) I am trying to do sorting using a YYYY-MM column, so I changed the calendar date column to YYYY-MM under column properties.

But it become multiple rows of 2021-11 instead of a single Nov row.

Once the yyyy-mm column is removed from the table layout.

It show the result I wanted.

Would it help if change this yyyy-mm column to a number format column?

If so, how do I do it in Enterprise Edition 11.1.1.7 analysis?

2) I tried to use concat (col1 || col 2), it work when the column are existing column from table.

Once I try to concat 2 column from "Calendar/Date functions", it gave error "formula syntax is invalid".

Any help is grateful, thank you so much.

Gianni Ceresa

Was the date column you used with a display mask of YYYY-MM of a lower granularity than your Year or Month column?

Because a display mask doesn't change the real value of a column: if it was at the day granularity, you probably have multiple days in your dataset for every year and month, and despite displaying only YYYY-MM those are all different values in the background (because different days).

What you need is to make it YYYY-MM in the background, not only on screen.

Ideally you should have that column coming from your data source directly, or you can build it in your RPD using the available OBIEE functions (there is a function to get the year from a date, there is one to get the month from a date, there is a function to convert numbers to chars and finally one to concatenate chars into a single piece of text. As an alternative you could use the numeric form with YEAR(date)*100+MONTH(date).

The key point is the granularity of your data, not how you make it looks like with a display mask.

TKQ5Q

Thank you so much for the explanation.

Yes, my original date is in calendar format. I followed your steps and concatenate chars into a single piece of text using the YYYY and MM from functions.

When I concat the 2 column using ||, there is also a space in-between, how do to get rid of the space? Thank you.

Gianni Ceresa

Isn't there a TRIM function? Or maybe it's still split between left and right trim. Just find where the space come from (is it because of the year, the month or an implicit conversion from numbers to chars?).