topic Sorting Legend in QlikView

Sorting Legend

cbaqir — Fri, 26 Feb 2016 15:19:28 GMT

This seems really simple and yet I can't seem to get it right. I have a requirement to sort a dimension in both the bar chart and the legend to match.

I have 3 dimensions:

1. (group) dPgmPm

2. Business Days: =Dual(If(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION), REQUEST_ID) <= 120, Class(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION), REQUEST_ID), 60), '>120'),
If(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION), REQUEST_ID) <= 120, Class(Aggr(Sum({<Flag_CurrentStep={1}>}WORKFLOW_DURATION), REQUEST_ID), 60), 120))
3.Curretn Step: =if(wildmatch(CUR_WORKFLOW_STEP_NAME,'Project Initiation and Design', 'Project Quality Review', 'Planning', 'Implementation', 'Close Out', 'Submission and Scoring', 'Demand Management Review', 'PRE Templating and Stratification', '*Funding*', 'Scheduling and Resourcing', '*Approval*', '*Hold*')>0,CUR_WORKFLOW_STEP_NAME,null())

I have 2 expressions:

1. Count of Requests: count(DISTINCT REQUEST_ID)

2. Label: =num(count(DISTINCT REQUEST_ID), '#,###') & ' Reqs'

The sorting is currently based on a numeric field called CUR_WF_SORT_ORDER :

1.dPgmPm: Y-value Descending

2. Business Days: Expression CUR_WF_SORT_ORDER Ascending

3. Current Step Expression CUR_WF_SORT_ORDER Ascending

However, while the sorting looks correct in the bar chart, it still doesn't look correct in the legend.

What should I do? I've been playing with dual(CUR_WORKFLOW_STEP_NAME, CUR_WF_SORT_ORDER) as sort order but I am not sure how to use dual.

Thanks,

Cassandra

Re: Sorting Legend

Peter_Cammaert — Fri, 26 Feb 2016 16:42:26 GMT

Just to be sure about your issue: the legend sorting doesn't match the barchart sorting in this picture?

Re: Sorting Legend

cbaqir — Fri, 26 Feb 2016 16:59:47 GMT

Correct. I want the legend to show in the same order as the bar chart (CUR_WF_SORT_ORDER).

Re: Sorting Legend

Peter_Cammaert — Fri, 26 Feb 2016 21:34:26 GMT

Can you please tell me, using the numbers displayed next to the legend items in my picture, what exactly the order should be?

If the wrong legend sorting is only happening in your attached qvw document (which wasn't clear from your OP), then simply press the Chart Properties->Presentation->Settings... button (in Legend group) and enable checkbox "Reverse Order".

Re: Sorting Legend

cbaqir — Fri, 26 Feb 2016 21:41:33 GMT

HI Peter,

Yes, I can do that. The displayed order is actually reversed. As you have them numbered, I would want to see 1-7.

I also updated the original post with a sample file that contains the sort order numbers in case you didn't see them.

Re: Sorting Legend

Peter_Cammaert — Fri, 26 Feb 2016 21:59:06 GMT

In both the attached document and the picture I expanded with numbers, the sort order of the legend is identical to the sort order of the stacked bars (give or take a "Reverse Order" checkmark). So my conclusion is that in your document, you want both sorted according to the number sequence from the CUR_WF_SORT_ORDER Excel sheet?

In that case, open Chart Properties->Sort, select "Workflow Step" from the list and in the Expression sort field, enter CUR_WF_SORT_ORDER. You'll get something like this (smallest number first).

BTW if you want to invert the sorting order, select "Descending" in the Expression sort order listbox.