Cycle Group Demension Sort Issue

[#1]

=if(Week < vCurrentWeek, Week, if(Week = vCurrentWeek, Week & ' - ' & DayOfWeek))

I currently have this as an expression within my date cycle group dimensions. It properly sorts the weeks from 13-1 (current week - previous). But the if statement says if it is the current week then load the days. So the result is as follows if it is current week 14 - 1 (1 being a day of week). However, the 14 shows up at the bottom of the weeks under 1 So when viewing the strait table the dimensions list as follows.

13
12
11
.
.
.
1
14 - 1
14- 2
14 - 3
14 -4

I want the 14 to be on top. Any suggestions?

Thanks

[#2]

Hello anthony, I think you are sorting in descending order, so probably you can try adding and space before the Week field of the previous weeks, it might not be clean but I should do the trick.

Regards.

=if(Week < vCurrentWeek, ' ' & Week, if(Week = vCurrentWeek, ' ' & Week & ' - ' & DayOfWeek))

[#3]

Sorry I added one extra space, this is what I meant.

=if(Week < vCurrentWeek, ' ' & Week, if(Week = vCurrentWeek, Week & ' - ' & DayOfWeek))

[#4]

Works, thank you.

[#5]

Actually that isn't working. Any other ideas/

[#6]

Mmm... probably you have to make sure you are sorting as text values, since the space is ascii 32 it should sort first than any letter, so if you sort Z -> A, the first result should be the higher ascii value, in this case week 14. If this is not working, you can try loading this dimmension into a table and create a sorting index column or a dual value.

Regards.

[#7]

You were right, to fix the problem i went in and changed it from numeric sort to alpha sort.

[#8]

Well, the issue doesn't seem to be resolved. Even when it was sorting the days before the weeks it was not sorting the days int he right order. Attached is a screen shot of what it is doing now. Starts at week 14 and goes to 1 then lists week 15 days. I want it to be in Descending order starting at the most recent week/day.

Any thoughts?

Attached is a screen shot of what it is doing.

below is my dimension code

= if(Week < vCurrentWeek, ' ' & Week, if(Week = vCurrentWeek, if(DayOfWeek < vDayOfWeek, Week & ' - ' & DayOfWeekAlpha)))

[#9]

Hello Anthony, can you post a sample qlikview application?

Regards

[johnw · Accepted solution]

How about using dual() to associate a numeric value with each value? For instance:

if(Week<vCurrentWeek,dual(Week,Week),if(Week=vCurrentWeek,dual(Week & ' - ' & DayOfWeek,Week+DayOfWeek/7)))

Then sort numerically.