topic Re: Sorting calculated dimension on Y axis in QlikView

Sorting calculated dimension on Y axis

Wed, 05 Mar 2014 10:16:46 GMT

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!

Re: Sorting calculated dimension on Y axis

MK_QSL — Wed, 05 Mar 2014 10:19:43 GMT

May be try this...

Dual (Your expression, Your Expression)

Re: Sorting calculated dimension on Y axis

Wed, 05 Mar 2014 10:28:44 GMT

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

tresB — Wed, 05 Mar 2014 10:33:15 GMT

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

=Class(Today()-eB_DocDatePlanned, 30)

Re: Sorting calculated dimension on Y axis

MK_QSL — Wed, 05 Mar 2014 10:41:42 GMT

Calculated Dimension....

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

Re: Sorting calculated dimension on Y axis

sudeepkm — Wed, 05 Mar 2014 10:42:37 GMT

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.

Re: Sorting calculated dimension on Y axis

Wed, 05 Mar 2014 10:43:35 GMT

Yes, the class function would be perfect for her request

Re: Sorting calculated dimension on Y axis

Wed, 05 Mar 2014 10:43:37 GMT

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

Wed, 05 Mar 2014 10:55:26 GMT

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

Sun, 22 Mar 2015 14:48:50 GMT

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