topic Re: Order of weekdates with string in QlikView

Order of weekdates with string

QlickySense — Wed, 29 Sep 2021 07:15:12 GMT

Hello,

I've created a dates to look like

Week(MyDate) & ' ' & 'Week' & Chr(13) & Year(MyDate)

However because I add 'Week' it becomes string and loses its order (without it its fine). However client really want its with "Week" or "W"

Any ideas?

Re: Order of weekdates with string

Vegar — Wed, 29 Sep 2021 07:27:03 GMT

You can create a dual of your field to solve this.

Try to create your field like this:

Dual(Week(MyDate) & ' ' & 'Week' & Chr(13) & Year(MyDate),monthname(MyDate))

Re: Order of weekdates with string

QlickySense — Wed, 29 Sep 2021 07:34:27 GMT

Hi,

unfortunately It didnt work

Re: Order of weekdates with string

Vegar — Wed, 29 Sep 2021 07:38:05 GMT

I am concerned about the calculation of your week-year. In some situations the calendar year is not the same as the week's year.

Week(MyDate) & ' ' & 'Week' & Chr(13) & Year(MyDate)

Eg: January 1th 2021 is in Week 53 2020, but your calculation will return the year 2021.

It is better to avoid using Year() and rather use the WeekYear() function when handling weeks. Like I've done below.

Week(MyDate) & ' ' & 'Week' & Chr(13) & WeekYear(MyDate)

Re: Order of weekdates with string

QlickySense — Wed, 29 Sep 2021 07:42:30 GMT

Thanks, I'll apply that. However still don't resolved sorting problem, with WeekYear it is the same, unfortunately

Re: Order of weekdates with string

Vegar — Wed, 29 Sep 2021 09:32:35 GMT

We misunderstand each other. I assumed you would use my suggestion to define your field in the script, replacing your current field.

Re: Order of weekdates with string

QlickySense — Wed, 29 Sep 2021 09:43:40 GMT

It converts nicely, but my problem is that if I get this field in X-axis it appears in random order like 4 Week 2020 and then 52 Week 2020, I'd like to order them by year and week

Re: Order of weekdates with string

Vegar — Wed, 29 Sep 2021 12:06:34 GMT

Check the object properties that your chart is ordered by your dual value dimension and that the sort order of your dimension is set to numeric sorting.