custom week sort order

[Nuckpio]

Hi everybody,

I have the following issue : the entreprise where I work wants me to sort weeks from 27 to 26 (27,28,29...52,1,2,3...26). I tried few things with WeekDay() function but nothing worked. Anybody has already solved this kind of problem? 

Sorry my english is terrible, I'll try to get better.

Thanks you in advance !

[JordyWegman · Accepted solution]

Hi Nuckpio,

This is no problem. Do you work with the scrip editor? When you do, you can use an ApplyMap to solve this.

mapWeekSort2Week:
Mapping Load * Inline [
Week, WeekSort
27,1
28,2
29,3
30,4
31,5
32,6
33,7
34,8
35,9
36,10
37,11
38,12
39,13
40,14
41,15
42,16
43,17
44,18
45,19
46,20
47,21
48,22
49,23
50,24
51,25
52,26
1,27
2,28
3,29
4,30
5,31
6,32
7,33
8,34
9,35
10,36
11,37
12,38
13,39
14,40
15,41
16,42
17,43
18,44
19,45
20,46
21,47
22,48
23,49
24,50
25,51
26,52
];

Then execute this ApplyMap in your calendar based on your Week field.

ApplyMap('mapWeekSort2Week',Week,0) as WeekSort

Then get your sorting based on an expression and this will be fixed.

Jordy

Climber

[Nuckpio]

Hi Jordy, thanks for responding.

Yes i work with the script editor. If i'm using this solution, my weeks will be rename, am I right? for example week 27 will become week 1 ?

I don't have any week field, only date field... this is why it's hard for me.

Thanks again.

[JordyWegman]

Hi,

That is not a problem, just use:

Calendar:
Load
   *,
   Week(Date) as Week
From [YourSource] (qvd)
;

Then add the following statement, maybe in a preceding load.

ApplyMap('mapWeekSort2Week',Week,0) as WeekSort

Jordy

Climber