Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

QlikSense second dimension legend order in line chart

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

6 Replies
brunobertels
Master
Master

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;

Not applicable
Author

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!

brunobertels
Master
Master

Hi

First i see that in your main script, the firstweekday is fixed to 6 ( saturday / Samstag) :

SET FirstWeekDay=6;

and in your mastercalandar :

WEEKSTART(TempDate, 0, 0) AS SubmitDateWeekStart, (third parameter give firstweekday, here monday

may be put the same parameter in main script and master calendar

in main script :

SET FirstWeekDay=0;

in master calandar :

WEEKSTART(TempDate, 0, 0) AS SubmitDateWeekStart,

Or 6 if you want saturday as fisrt day of week.

bruno

Not applicable
Author

Ah, I hadn't noticed that SET FirstWeekDay.  I think that page was the starter template for a new app.  Unfortunately, changing that doesn't seem to have any effect on the ordering of the weekdays.

brunobertels
Master
Master

Hi

Ok sorry to hear that.

I don't know how to help you more.

Sorry

Bruno

daniel_sonda
Contributor II
Contributor II

Same problem here. Have you got any solution?

It seems to be ordered by the expression.