Sorting calculated dimension on Y axis

[#1]

Hello,

I have created a calculated dimension for my bar chart as an If statement:

=

if(eB_DocDatePlanned >= Today() and eB_DocDatePlanned <= Today()+30,'+30 days',
if(eB_DocDatePlanned >= Today()+31 and eB_DocDatePlanned <= Today()+60,'+31 to 60 days',
if(eB_DocDatePlanned >= Today()+61 and eB_DocDatePlanned <= Today()+90,'+61 to 90 days',
if(eB_DocDatePlanned >= Today()+91 and eB_DocDatePlanned <= Today()+120,'+91 to 120 days',
if(eB_DocDatePlanned < Today() and eB_DocDatePlanned >= Today()-30,'-30 days',
if(eB_DocDatePlanned <= Today()-31 and eB_DocDatePlanned >= Today()-60,'-31 to 60 days',
if(eB_DocDatePlanned <= Today()-61 and eB_DocDatePlanned >= Today()-90,'-61 to 90 days',
if(eB_DocDatePlanned <= Today()-91 and eB_DocDatePlanned >= Today()-120,'-91 to 120 days'))))))))

I am now having a difficult time sorting the dimension.

Sorting by Numeric Value, ascending, I see the following order:

-31 to 60 days; -61 to 90 days; -91 to 120 days; -30 days; +30 days; +91 to 120 days; +31 to 60 days; +61 to 90 days

Sorting by Text, A -> Z, I see the following order:

-30 days; -31 to 60 days; -61 to 90 days; -91 to 120 days; +30 days; +31 to 60 days; +61 to 90 days; +91 to 120 days

Any ideas on how to get the correct order:

-91 to 120 days; -61 to 90 days; -31 to 60 days; -30 days; +30 days; +31 to 60 days; +61 to 90 days; +91 to 120 days

Thanks for your input!

[MK_QSL]

May be try this...

Dual (Your expression, Your Expression)

[#3]

Not sure, but maybe this might work

=WildMatch(valuelist('1','2','3','4'),1,2,3,4)

replace the valuelist text values with your if statement output i.e. '+30 Days' and then the match order

Write this in the sort tab and sort by expression

Maybe simply sorting by expression and listing each of your dimension values in order that you want to sort

'+30 Days', '+60 Days','+90 Days' et cetera

[tresesco]

I would suggest to use Class() function that would make things easier.

=Class(Today()-eB_DocDatePlanned, 30)

[MK_QSL]

Calculated Dimension....

=DUAL(Replace(Class(Today()-eB_DocDatePlanned ,30),'<= x <',' - '),Class(Today()-eB_DocDatePlanned,30))

[sudeepkm]

I think you can

1. Create a "duration flag" field (suppose name it as docPlannedDur) based on the logic you mentioned in your load script.

2. Before your actual table load script create an inline load like below (following the sorting order you need)

DurFlg:

load * inline [

docPlannedDur

+30 days

+31 to 60 days

+61 to 90 days];

3. After your actual load script create a drop table statement for DurFlg table.

drop table DurFlg;

4. In the Chart add the new field docPlannedDur as dimension and then under Sort tab use Load Order Original

Attached is the QVW for your reference.

[#7]

Yes, the class function would be perfect for her request

[#8 · Accepted solution]

Hi,

You can also try this in Sort expression:

only({1}if(eB_DocDatePlanned >= Today() and eB_DocDatePlanned <= Today()+30,5,

if(eB_DocDatePlanned >= Today()+31 and eB_DocDatePlanned <= Today()+60,6,

if(eB_DocDatePlanned >= Today()+61 and eB_DocDatePlanned <= Today()+90,7,

if(eB_DocDatePlanned >= Today()+91 and eB_DocDatePlanned <= Today()+120,8,

if(eB_DocDatePlanned < Today() and eB_DocDatePlanned >= Today()-30,4,

if(eB_DocDatePlanned <= Today()-31 and eB_DocDatePlanned >= Today()-60,3,

if(eB_DocDatePlanned <= Today()-61 and eB_DocDatePlanned >= Today()-90,2,

if(eB_DocDatePlanned <= Today()-91 and eB_DocDatePlanned >= Today()-120,1))))))))

)

[#9]

Thank you very much all!! Srashti's response worked the quickest for me, though I have no doubt all contributions are valid

[#10]

http://dbmanagement.info/Tutorials/Oracle_EBS.htm

Oracle EBS- Video Training Tutorial DVDs (2 DVDs)

More than 8 GB of Video files Sale Price 19.97