Get the Week Number Starting from the Current week

[Anonymous]

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.

[Digvijay_Singh · Accepted solution]

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/22/2017

5/22/2017

5/22/2017

5/29/2017

5/29/2017

5/29/2017

5/29/2017

5/29/2017

6/5/2017

6/5/2017

6/5/2017

6/5/2017

6/12/2017

6/19/2017

6/26/2017

7/3/2017

7/10/2017 ];

[Anonymous]

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

[Digvijay_Singh]

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.