topic Re: Problem sorting dimension with generated dual date in QlikView

Problem sorting dimension with generated dual date

benjamins — Thu, 29 Aug 2013 07:16:15 GMT

I'm using a dual Year&Week as the only dimension of a bar chart (see attachment).

I load Year&Week in script as

Dual(Year(DateId)&' '&Week(DateId),WeekStart(DateId)) as YearWeek

DateId is a plain date

I set the dimension's sorting order to numeric ascending as I would have expected the numeric value of the dual YearWeek to be used. However this does not seem to be the case as the result is:

2012 52

2012 1

2012 2

2012 3

...

2013 51

2013 52

2013 1

When loading years 2012 and 2013.

I've tried lots of different approaches from using other sorting orders up to interpreting YearWeek as string only. None of which yielded a correct result.

Anyone any idea what I'm overlooking?

Thanks and cheers

Benjamin

Re: Problem sorting dimension with generated dual date

Thu, 29 Aug 2013 07:26:55 GMT

I think that your problem there is that you are using the year() method instead of the WeekYear() method.

Example with 1 of January of 2012:

Year(): 2012

WeekYear(): 2011

Week(): 52 --> Because that day is considered part of the las week of 2011.

Re: Problem sorting dimension with generated dual date

jfkinspari — Thu, 29 Aug 2013 07:30:39 GMT

The reason to why it's sorting as it is, is because of the output of the function Weekstart, which you use in the Dual formula. I expect you think it gives an integer, but's it's actual giving a date, and as 1st Jan 2012 belongs to a week starten in 2011, it gives the smallets value. Further more it's has to do with 1st Jan 2012 belongs to weeknumber 52 - given the used method.

Re: Problem sorting dimension with generated dual date

benjamins — Thu, 29 Aug 2013 07:40:57 GMT

Thanks to the both of you for your explanations!

So,

Dual(WeekYear(DateId)&' '&Week(DateId),Num(Date#(WeekStart(DateId)))) as YearWeek,

seems to yield correct results. However I'm a bit confused as to wether this really is the correct usage of the Dual() function now!? I would have thought that passing an actual date as the function's second argument would cause the numeric representation to be used.

Re: Problem sorting dimension with generated dual date

CELAMBARASAN — Thu, 29 Aug 2013 07:46:10 GMT

Try with this Expression for your case

Dual(Year(DateId)&' '&Week(DateId),Num(Year(DateId)&Week(DateId))) as YearWeek,

You cann't use week start here.

Re: Problem sorting dimension with generated dual date

jfkinspari — Thu, 29 Aug 2013 07:46:42 GMT

The second part of the dual funktion, Num(Date#(WeekStart(DateId)))), will make no difference.

It's indeed using the numeric rep.

I think you'll be good just be replacing the year function as IOSU explained

Re: Problem sorting dimension with generated dual date

kaushiknsolanki — Thu, 29 Aug 2013 07:50:59 GMT

Hi,

Do one thing, create another filed called Week.

Now in your chart go to the sort tab and choose the expression.

Here enter the field name week and sort it in ascending order.

And remove other sorting options.

Hope this will solve your problem.

Regards,

Kaushik Solanki

Re: Problem sorting dimension with generated dual date

benjamins — Thu, 29 Aug 2013 07:52:20 GMT

Nope, this expression produces very strange sorting

Re: Problem sorting dimension with generated dual date

benjamins — Thu, 29 Aug 2013 08:00:12 GMT

Nope, produces sorting as seen below

Re: Problem sorting dimension with generated dual date

benjamins — Thu, 29 Aug 2013 08:01:21 GMT

Thank you very much everyone. You saved me HOURS!

Re: Problem sorting dimension with generated dual date

CELAMBARASAN — Thu, 29 Aug 2013 08:02:22 GMT

Hi,

Try with this

Dual(Year(DateId)&' '&Week(DateId),Num(Year(DateId)&Num(Week(DateId),'00'))) as YearWeek,

Re: Problem sorting dimension with generated dual date

kaushiknsolanki — Thu, 29 Aug 2013 08:04:52 GMT

Hi,

I guess nothing is wrong in your sorting, only thing is the year field which you have created is wrong.

Let me give you an example.

You have written

Dual(Year(DateId)&' '&Week(DateId),WeekStart(DateId)) as YearWeek,

So now consider the date 01.01.2012.

The week for this date is 52 (which is of last year i.e year 2011) but in your field it shows as 2012 52, which is wrong, correct is 2011 52.

So change your expression to.

Dual(Year(weekstart(DateId))&' '&Week(DateId),WeekStart(DateId)) as YearWeek

Hope this is clear to you.

Regards,

Kaushik Solanki