Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

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

)

9 Replies

Re: Sorting calculated dimension on Y axis

May be try this...

Dual (Your expression, Your Expression)

Not applicable

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

MVP
MVP

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

Calculated Dimension....

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

sudeepkm
Valued Contributor III

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:

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

Re: Sorting calculated dimension on Y axis

Yes, the class function would be perfect for her request

Not applicable

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

)

Not applicable

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

Not applicable

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

Community Browser