topic Re: Get the Week Number Starting from the Current week in QlikView

Get the Week Number Starting from the Current week

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

Hello!

I hope someone can help me on this. I have a series of Mon dates that are not in order in a given table where there are also a bunch of other fields . I want to assign a week number to start at the current Mon Date. If the Monday Date < today's Monday date, 0,

If today is current Mon Date, 1 otherwise Week 1+2 and so on...

I tried using the peek function but it's giving me the right values/

If(weekstart(Mon_Date) < weekstart(today()),0 ,
IF(weekstart(Mon_Date) = weekstart(today()),1,
peek([Week Number])+1 ))as [Week Number]

I tried using the peek function but output is different from what I expected ...Below is my expected output.

Thank you in advance.

Re: Get the Week Number Starting from the Current week

Digvijay_Singh — Thu, 01 Jun 2017 00:19:48 GMT

This works with your sample data -

Load MondayDate,

If(weekstart(MondayDate) < weekstart(today(1)),0,

If(weekstart(MondayDate) = weekstart(today(1)),1,

If(weekstart(MondayDate)<> weekstart(Peek(MondayDate)),rangesum(peek([Week Number]),1),Peek([Week Number])))) as [Week Number]

inline [

MondayDate

5/22/2017

5/29/2017

6/5/2017

6/12/2017

6/19/2017

6/26/2017

7/3/2017

7/10/2017 ];

Re: Get the Week Number Starting from the Current week

Anonymous — Thu, 01 Jun 2017 22:49:02 GMT

Hi! Digvijay,

It actually works. Thank you so much ...I've been stuck on this for days. Anyway, If you could explain to me the 3rd IF statement , it would be great....it's quite confusing for beginners like me. Also, why are your using today(1)? What's the difference with today()?

Rgds

Re: Get the Week Number Starting from the Current week

Digvijay_Singh — Thu, 01 Jun 2017 23:24:54 GMT

The control comes to third IF only when the date is higher than today() but if the previous row date is same as current row date, we don't want to add 1 to the count. So third IF keeps counter same as long as new row date is same as previous row date.

Today(1) take the date of function called. Not sure of default , the help says -

Can have the following values:

0 (day of last finished data load)
1 (day of function call)
2 (day when the app was opened)

If you use the function in a data load script, timer_mode=0 will result in the day of the last finished data load, while timer_mode=1 will give the day of the current data load.