9 Replies Latest reply: Mar 22, 2015 10:48 AM by si si

# Sorting calculated dimension on Y axis

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

• ###### Re: Sorting calculated dimension on Y axis

May be try this...

• ###### Re: Sorting calculated dimension on Y axis

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

• ###### Re: Sorting calculated dimension on Y axis

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

=Class(Today()-eB_DocDatePlanned, 30)

• ###### Re: Sorting calculated dimension on Y axis

Yes, the class function would be perfect for her request

• ###### Re: Sorting calculated dimension on Y axis

Calculated Dimension....

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

• ###### Re: Sorting calculated dimension on Y axis

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:

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.

• ###### Re: Sorting calculated dimension on Y axis

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))))))))

)

• ###### Re: Sorting calculated dimension on Y axis

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

• ###### Re: Sorting calculated dimension on Y axis

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