6 Replies Latest reply: Oct 13, 2016 4:02 PM by Daniel Sonda RSS

    QlikSense second dimension legend order in line chart

    Hank Haspy

      Hi folks,

       

      I have a line chart in which I'm showing the performance based on the day of the week of, for example, account submission.  My first dimension is the WeekStart, my second dimension is the WeekDay, and the measure is the sum of submission amounts.  It's a fine looking graph, but the legend never seems to display in the proper order, making it somewhat confusing to try to read.  Worse yet, as I make selections, the order of the legend (and thus the colors of the weekdays) may change.

       

      ScreenHunter_42 Feb. 10 21.38.gif

       

      Because it's my second dimension on the chart, I can't move SubmitDateWeekDay above SubmitDateWeekStart in the Sorting tab.  And I'm just using the WeekDay() function on a date, so it's already a Dual (which I confirmed by using Num(SubmitDateWeekDay) instead of just SubmitDateWeekDay), so I don't think that's affecting the ordering. 

       

      Is there any way to force the second dimension and thus the legend to display in one fixed order?  I've tried searching the community but everything seems to be for QlikView and nothing seems to work.

       

      I'm attaching a .qvf so you can see what I used to generate the above.

       

      Thanks for any input!

      Hank

        • Re: QlikSense second dimension legend order in line chart
          bruno bertels

          Hi

           

          May be it's due to load order in your script

           

          Try this in your script :

           

          Submissions:

          LOAD

              GroupID,

              AccountID,

              WorkerID,

              SubmitDate,

              DaysSinceSubmission,

              SubmitAmount

          FROM [lib://Downloads/QlikBucketTest.xlsx]

          (ooxml, embedded labels, table is Sheet1);

          //order by SubmitDate;

           

           

          Workers:

          LOAD

              WorkerID,

              CompanyID,

              FirstName,

              LastName

          FROM [lib://Downloads/QlikBucketTest.xlsx]

          (ooxml, embedded labels, table is Sheet2);

           

          Submissions1:

          load

          *

          resident Submissions

          order by SubmitDate;

          drop table Submissions;

            • Re: QlikSense second dimension legend order in line chart
              Hank Haspy

              Hey Bruno, thanks for the suggestion!  Unfortunately, when I try that, I get an error later on in the MasterCalendar section that the table can't be found.  I get that even when I name the original table Submssions1 and the second table Submissions and then drop Submissions1.  But even when I don't drop any tables, or I do something to definitely load in the right SubmitDateWeekDay first, like by adding this at the top:

               

              WeekDayOrder:

              LOAD WEEKDAY(daynum) AS SubmitDateWeekDay, Dual(german, daynum) AS GermanWeekDay INLINE [

              daynum, german

              1, 'Sonntag'

              2, 'Montag'

              3, 'Dienstag'

              4, 'Mittwoch'

              5, 'Donnerstag'

              6, 'Freitag'

              7, 'Samstag'

              ];

               

              it still doesn't order things right. Plus, if I'm ordering based on my data, I'm not sure that the first date will be a Sunday or a Monday, so it may still have a funny order.

               

              Is there something I'm missing about the load order, or is there some other way to approach this?

               

              Thanks again!

            • Re: QlikSense second dimension legend order in line chart
              Daniel Sonda

              Same problem here. Have you got any solution?

               

              It seems to be ordered by the expression.