topic Cycle Group Demension Sort Issue in QlikView

Cycle Group Demension Sort Issue

Fri, 09 Apr 2010 18:14:22 GMT

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

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

Thanks

Cycle Group Demension Sort Issue

Fri, 09 Apr 2010 19:46:12 GMT

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

Cycle Group Demension Sort Issue

Fri, 09 Apr 2010 19:47:03 GMT

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

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

Cycle Group Demension Sort Issue

Fri, 09 Apr 2010 20:04:08 GMT

Works, thank you.

Cycle Group Demension Sort Issue

Fri, 09 Apr 2010 20:26:18 GMT

Actually that isn't working. Any other ideas/

Cycle Group Demension Sort Issue

Fri, 09 Apr 2010 21:28:47 GMT

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.

Cycle Group Demension Sort Issue

Sat, 10 Apr 2010 17:41:41 GMT

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

Cycle Group Demension Sort Issue

Mon, 19 Apr 2010 14:27:29 GMT

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

Cycle Group Demension Sort Issue

Mon, 19 Apr 2010 22:03:13 GMT

Hello Anthony, can you post a sample qlikview application?

Regards

Cycle Group Demension Sort Issue

johnw — Tue, 20 Apr 2010 01:18:30 GMT

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.

Cycle Group Demension Sort Issue

Tue, 20 Apr 2010 14:04:40 GMT

John, that works. except now it is showing mon-fri even though I want it to only show currentday -1 (yesterday).

Any thoughts?

Thanks,

Cycle Group Demension Sort Issue

Tue, 20 Apr 2010 14:07:04 GMT

Actually i just added back my if statement in your example. Works great, thank you!