topic Qlikview and week 53 year 2016? in QlikView

Qlikview and week 53 year 2016?

Thu, 24 Mar 2016 08:34:48 GMT

Hello!

I am having a big trouble with my QlikView viz.

The viz is showing now weekly based trendlines and we are compering lines between years.

The problem is that the year 2016 line continues all the way to the week 53, that obviously has not yet reached for this year.

This is some ISO-date problems within the program.. Is there any solutions how this can be fixed?

I am using the QlikView Mastercalendar code to generate dates based on my own datefield.

The orange line is the problem here... The straight line from week 11 to the week 53 should not be there.

Cheers,

Niko

Re: Qlikview and week 53 year 2016?

swuehl — Thu, 24 Mar 2016 10:24:10 GMT

I think you should use a year field for your graph created with WeekYear() function, instead of just Year():

weekyear(date)

The year to which the week number belongs. The week number ranges between 1 and approximately 52. Some years week #1 starts in December, e.g. December 1997. Other years start with week #53 of previous year, e.g. January 1999. For those few days when the week number belongs to another year, the functions year and weekyear will return different values.

Examples:

weekyear( '1996-12-30' ) returns 1997.

weekyear( '1997-01-02' ) returns 1997.

weekyear( '1997-12-30' ) returns 1997.

weekyear( '1999-01-02' ) returns 1998.

Re: Qlikview and week 53 year 2016?

Thu, 24 Mar 2016 12:39:18 GMT

Hello,

Thank you for your input, I will look on to that.

I currently have two dimensions for time and they are as follows in this order:

=if(len(WeekYear)=6,left(WeekYear,1),left(WeekYear,2))

=right(WeekYear,4)

And the WeekYear is a field from the MasterCalendar.

Cheers,

Niko

Re: Qlikview and week 53 year 2016?

swuehl — Thu, 24 Mar 2016 13:19:25 GMT

How is WeekYear created in the script, i.e. is the Year based on Year(Date) or WeekYear(Date)?

Re: Qlikview and week 53 year 2016?

Thu, 24 Mar 2016 13:24:46 GMT

Hello,

It is generated like Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

Cheers,

Niko

Re: Qlikview and week 53 year 2016?

swuehl — Thu, 24 Mar 2016 13:47:08 GMT

I can't reproduce the issue with some simple data, the line only gets to week 53 in year 2016 when the 'standard' year is used.

Could you upload a small sample QVW?

edit: Attached my sample.

Re: Qlikview and week 53 year 2016?

Tue, 29 Mar 2016 07:16:57 GMT

Hmm, thank you for your sample workbook.

I am also having 3 different expressions on my chart, may that 'cause the false line on the chart?

This is expression for the line that shows two years back:

=if(isNull(GetFieldSelections(Year)),sum({<Year={"$(vMaxYearMinus2)"}, Week=>} #1), sum({<Year={$(=Max(Year-2))}, Week=>}#1))

I have these expressions 3 times, for each "year". Current, current-1 and current-2.

And this is for that the lines will dynamically change when user makes selections. And as a default we show the value with variable.

Maybe not the best way to do it, but it works for the purpose

Cheers,

Niko

Re: Qlikview and week 53 year 2016?

Tue, 29 Mar 2016 07:18:49 GMT

Yep, when I removed the 3 custom expressions, it showed the year 2016 without the long line to the week 53.

But this removes my filtering for 3 top years ...

Cheers,

Niko

Re: Qlikview and week 53 year 2016?

Tue, 29 Mar 2016 07:23:44 GMT

Well actually when selecting year 2016 from the filters, it will show a dot on the timeline for week 53 alltho' it is colored for year 2015. Odd.

Cheers,

Niko

Re: Qlikview and week 53 year 2016?

swuehl — Tue, 29 Mar 2016 13:41:38 GMT

Could you update my sample file that it matches your setting and demonstrates your issue?

Then upload it to this thread, please.

Re: Qlikview and week 53 year 2016?

Wed, 30 Mar 2016 05:34:55 GMT

Hello,

I attached a new file. On this file you can find on the right upper corner my issue with the three expressions. It just continues to the the week 53.

Cheers,

Niko

Re: Qlikview and week 53 year 2016?

Wed, 30 Mar 2016 08:48:37 GMT

In your expression you are still referring to the Year (based on month) instead of the Year of the week (in your example defined as WeekYear2) for your Set Analysis. If you are referring to WeekYear2 instead, everything should work fine:

=if(isNull(GetFieldSelections(WeekYear2)),sum({<WeekYear2={"$(vMaxYear)"}, Week=>} Value), sum({<WeekYear2={$(=Max(Vuosi))}, Week=>} Value))

Re: Qlikview and week 53 year 2016?

swuehl — Wed, 30 Mar 2016 10:02:56 GMT

Hi Niko,

I think you should also use a WeekYear field in your set expression field modiifer then:

=if(isNull(GetFieldSelections(Year)),sum({<WeekYear2={"$(vMaxYear)"}, Week=>} Value), sum({<WeekYear2={$(=Max(Vuosi))}, Week=>} Value))

Maybe your GetFieldSelections should also check on a WeekYear field, not sure what this expression should actually do.

Re: Qlikview and week 53 year 2016?

Wed, 30 Mar 2016 10:12:00 GMT

Hello swuehl,

Well that indeed does the trick! Now I have to wonder should I use that to all my expressions and calculations or only in this chart.. There would be many expressions to change here, but that truly did change it.

I had to create WeekYear field on the editor load for this just from the temdate, just like you had done.

The if(isnull) check is just for there if user has done any selections or not. If not, then we show max year and if he makes year selections we show the max year from those selections. For example the selection is 2002-2005, we show 2005 on this line.

Cheers,

Niko

Re: Qlikview and week 53 year 2016?

stantrolav — Thu, 29 Sep 2016 16:06:33 GMT

num(Week(DateNum), '00') as CalWeek52,

num(if(Week(DateNum) = 1 and Month(DateNum) = 12, 53, if(Week(DateNum) = 53 and Month(DateNum) = 1, 1,Week(DateNum))), '00') as CalWeek53,