Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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;
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!
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
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.
Hi
Ok sorry to hear that.
I don't know how to help you more.
Sorry
Bruno
Same problem here. Have you got any solution?
It seems to be ordered by the expression.