topic Re: Transform W201613 to Week number like 13 in QlikView

Transform W201613 to Week number like 13

0li5a3a — Fri, 21 Jul 2017 19:08:41 GMT

Hi,

Does anyone know if I can transform in a Line Chart / Dimension the week format from W201613 to 13?

Regards,

Re: Transform W201613 to Week number like 13

zhadrakas — Sat, 22 Jul 2017 09:28:07 GMT

hello Constantin,

try this in Dimension.

right(Week,2)

regards

tim

Re: Transform W201613 to Week number like 13

0li5a3a — Sat, 22 Jul 2017 17:22:55 GMT

Hi this works for me now thanks. Now i just discovered that the weeks are not in the right possition.

I have something like this

The problem is... everything before 11 is 2017 data and all the rest are 2016 ... I used in the expression this calculation =count({$<Status= {'WP'}, CalendarPeriodnum={'>= $(=num(WeekStart(AddWeeks(max(CalendarPeriod),-15)))) <= $(=num(WeekEnd(max(CalendarPeriod))))'},YrWeek=,CalendarYear=>}TICKETID) and I think.

Any advices ?

I want the maximum week - 16

Re: Transform W201613 to Week number like 13

zhadrakas — Mon, 24 Jul 2017 06:20:17 GMT

can you share a sample qvw?

Re: Transform W201613 to Week number like 13

0li5a3a — Mon, 24 Jul 2017 07:21:13 GMT

I can't do this.... I think my Yrweek is not right and maybe i need to create a new YRWeek and order them asc. it is this right?

Re: Transform W201613 to Week number like 13

zhadrakas — Mon, 24 Jul 2017 07:27:47 GMT

i think max(CalendarPeriod) is the Problem at this Point. Please check this value.

Re: Transform W201613 to Week number like 13

0li5a3a — Mon, 24 Jul 2017 08:15:30 GMT

I changed the dimension into =DATE#(date#(RIGHT(OP.YrWeek,2))) and the expression into =count({$<Status= {'WP'}>}TICKETID) and now I have this:

This is right now only the problem is I want to display only the last 15 weeks. I tried the dimension limits but all the files with no data would go and I want to show the weeks with no data to.

Re: Transform W201613 to Week number like 13

zhadrakas — Mon, 24 Jul 2017 08:34:06 GMT

you could try this as Dimension:

=if(CalendarPeriodnum>= num(WeekStart(AddWeeks(max(CalendarPeriod),-15)))
AND CalendarPeriodnum <= num(WeekEnd(max(CalendarPeriod))), DATE#(date#(RIGHT(OP.YrWeek,2))))

Leave the Checkbox "Suppres Null Value" unticked to Display other weeks too.

Re: Transform W201613 to Week number like 13

0li5a3a — Mon, 24 Jul 2017 09:06:54 GMT

I think something is wrong with the above code because the AddWeeks is highlighted with red and all the function after this they are not colored in blue...

Could you please write all the code including my condition for a specific status?

This is my dimension =count({$<Status= {'WP'}, CalendarPeriodnum={'>= $(=num(WeekStart(AddWeeks(max(CalendarPeriod),-15)))) <= $(=num(WeekEnd(max(CalendarPeriod))))'},YrWeek=,CalendarYear=>}TICKETID)

and I tried to include your code like this:

=count({$<Status= {'WP'},=if(CalendarPeriodnum>= num(WeekStart(AddWeeks(max(CalendarPeriod),-15)))
AND CalendarPeriodnum <= num(WeekEnd(max(CalendarPeriod))), DATE#(date#(RIGHT(OP.YrWeek,2))))>},TICKETID)

Re: Transform W201613 to Week number like 13

0li5a3a — Mon, 24 Jul 2017 09:32:04 GMT

Please ignore my preview replay. I tried now in the dimension and the AddWeeks highlighted with red and all the function after this they are not colored in blue...

Re: Transform W201613 to Week number like 13

0li5a3a — Mon, 24 Jul 2017 09:42:09 GMT

Please ignore my preview replay. I tried now in the dimension and the AddWeeks highlighted with red and all the function after this they are not colored in blue... I think the syntax is wrong

Please find below:

Re: Transform W201613 to Week number like 13

zhadrakas — Mon, 24 Jul 2017 12:25:48 GMT

just typed this Expression without testing. There is no addweeks function in QlikView

try to use addmonths instead (-4 shoud be equivalent to 16 weeks)

=if(CalendarPeriodnum>= num(WeekStart(AddMonths(max(CalendarPeriod),-4)))
AND CalendarPeriodnum <= num(WeekEnd(max(CalendarPeriod))), DATE#(date#(RIGHT(OP.YrWeek,2))))

Re: Transform W201613 to Week number like 13

Anonymous — Mon, 24 Jul 2017 13:07:07 GMT

Use something like : right(Week,2)

Re: Transform W201613 to Week number like 13

0li5a3a — Mon, 24 Jul 2017 13:56:20 GMT

I used this and all the data just gone....

=if(CalendarPeriodnum>= num(WeekStart(AddMonths(max(CalendarPeriod),-4))) AND CalendarPeriodnum <= num(WeekEnd(max(CalendarPeriod))), DATE#(date#(RIGHT(OP.YrWeek,2)))). Is an error into dimension formula