topic Re: dynamic axis in QlikView

dynamic axis

Frank_Hartmann — Wed, 25 Nov 2020 16:16:04 GMT

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,

];

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!

Re: dynamic axis

swuehl — Fri, 17 Jun 2016 21:30:26 GMT

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,

];

Re: dynamic axis

Frank_Hartmann — Fri, 17 Jun 2016 21:33:29 GMT

it works!

thank you very much

Re: dynamic axis

Anonymous — Fri, 17 Jun 2016 22:28:29 GMT

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

];

Regards,

-- Karla

Re: dynamic axis

Frank_Hartmann — Sun, 19 Jun 2016 15:14:34 GMT

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 🙂

Re: dynamic axis

swuehl — Sun, 19 Jun 2016 23:29:44 GMT

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

Re: dynamic axis

Frank_Hartmann — Mon, 20 Jun 2016 04:48:37 GMT

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

Re: dynamic axis

swuehl — Mon, 20 Jun 2016 08:42:59 GMT

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

Re: dynamic axis

Frank_Hartmann — Mon, 20 Jun 2016 09:39:02 GMT

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?

Re: dynamic axis

swuehl — Mon, 20 Jun 2016 10:05:39 GMT

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.

Re: dynamic axis

Frank_Hartmann — Mon, 20 Jun 2016 10:13:57 GMT

Super, vielen Dank für deine Hilfe!