5 Replies Latest reply: Feb 26, 2016 5:00 PM by Peter Cammaert RSS

    Sorting Legend

    Cassandra Baqir

      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.

      2-26-2016 8-12-48 AM.jpg



      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.


      2-26-2016 8-18-08 AM.jpg


      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.




        • Re: Sorting Legend
          Peter Cammaert

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


          Sorting mismatch thread207220.jpg

            • Re: Sorting Legend
              Cassandra Baqir

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

                • Re: Sorting Legend
                  Peter Cammaert

                  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
                      Cassandra Baqir

                      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

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


                          Sorting mismatch v2 thread207220.jpg

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