dynamic axis

[Frank_Hartmann]

Hi Community,

I would like to create a dynamic sortorder axis in script.

that means that the sortorder should be linked to actual month.

If we have Month = 06 now, then for Sortorder it should be the last value (=12)

and Month = 07 should be the first value (=1) for sorting.

In the next month, 07 should be the last value for sorting (=12) and Month = 08

should be the first value (=1) for sorting!

I tried a for next loop in script, but i only managed  to get it work for seven values.

My script:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

test:

LOAD * INLINE [

    Month,

    01,

    02,

    03,

    04,

    05,

    06,

    07,

    08,

    09,

    10,

    11,

    12

];

For i = 0 to 11

test1:

Load

    Month,

    if(Month=Month(now())-$(i) and Month(now())-$(i)>0,12-$(i),if(Month-Month(now())-$(i)=0 and Month(now())-$(i)=0,12-$(i))) as Sortorder  

Resident test;

Next i;

DROP Table test;

ExpectedOutput:

LOAD * INLINE [

    MonthEO, SortoderEO

    01, 7

    02, 8

    03, 9

    04, 10

    05, 11

    06, 12

    07, 1

    08, 2

    09, 3

    10, 4

    11, 5

    12, 6

];

Any ideas how to achieve that for all 12 values?

Every help will be appreciated!

[swuehl · Accepted solution]

Try

test:

LOAD *, Mod(Month-(Month(Today(1))+1),12)+1 as SortOrder;

LOAD * INLINE [

    Month,

    01,

    02,

    03,

    04,

    05,

    06,

    07,

    08,

    09,

    10,

    11,

    12

];

[Frank_Hartmann]

it works!

thank you very much

[Anonymous]

Hi Frank,

You could try something like this:

LET vOffset = 12 - Num(Month(Today()));

LET vCurrentMonth = Num(Month(Today()));

LOAD

  If(Month > $(vCurrentMonth), Month - $(vCurrentMonth), Month + $(vOffset)) as Corr,

  Month;

LOAD * INLINE [

    Month

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

];

Regards,

-- Karla

[Frank_Hartmann]

Hi,

i try to apply your formula to sort a range of 26 weeks.

The probleme is that:

Mod(53-(week(Today(1))+1),26)+1=3

Mod(1-(week(Today(1))+1),26)+1=3

Do you have a clue how to adapt the Mod function?

thank u in advance 🙂

[swuehl]

To sort a range of 26 weeks? And how do you want to sort the range?

[Frank_Hartmann]

this should be the sortorder:

WeekAsNumber	Sortorder
52	1
53	2
1	3
2	4
3	5
4	6
5	7
6	8
7	9
8	10
9	11
10	12
11	13
12	14
13	15
14	16
15	17
16	18
17	19
18	20
19	21
20	22
21	23
22	24
23	25
24	26

thx for helping

[swuehl]

And how does this look like in a few weeks / months? Or for last year?

[Frank_Hartmann]

Yes, i think thats the probleme.

I attached a sample with my script and datamodel!

The requirement is to always take the last 26 weeks for sorting, respective week(now()) as last value (=26).

That means if i use the slider than the "week(now())" should be connected with my variable "vAktuelleKW", so that the sortorder dynamically adapts. Any idea how to achieve that?

[swuehl]

Ich würde im Mastercalendar ein numerisches Feld anlegen, dass alle Wochen im Kalender durchnummeriert.

Dann kannst du auf diesem Feld deinen Kalenderbereich selektieren und das gleiche Feld zum sortieren benutzen.

Vielleicht so wie im Anhang.