Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

Thanks for your input!

1 Solution

Accepted Solutions
Not applicable
Author

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

)

View solution in original post

9 Replies
MK_QSL
MVP
MVP

May be try this...

Dual (Your expression, Your Expression)

Not applicable
Author

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

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

=Class(Today()-eB_DocDatePlanned, 30)

MK_QSL
MVP
MVP

Calculated Dimension....

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

sudeepkm
Specialist III
Specialist III

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

loadorder.png

Attached is the QVW for your reference.

Not applicable
Author

Yes, the class function would be perfect for her request

Not applicable
Author

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

)

Not applicable
Author

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

Not applicable
Author

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