Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
ananyaghosh
Contributor III

how to calculate the week number from a date?

Hi,

I am using the below code to calculate previous week number based on current date:

Let vPreviousWeekID = replace(weekname(today(),-1),'/','-W');

trace 'Previous WeekID = ' & $(vPreviousWeekID);


and it gives me this results:


Previous WeekID = & 2017-W15


which is incorrect and it should be: Previous WeekID = & 2017-W17

please give me a suggestion that what is wrong here?

Thanks,

Sandip Ghosh

Tags (1)
15 Replies
qlikview979
Valued Contributor

Re: how to calculate the week number from a date?

Hi

Let vPreviousWeekID = replace(weekname(today(),-1),'/','-W');

How did you  got  Previous WeekID = & 2017-W15,



for me its showing Previous WeekID = & 2017-W16

Re: how to calculate the week number from a date?

Some useful tips:

  • QlikView uses the ISO week number logic, not the one from MS Outlook or other applications. This means that we are now in week 17, not 18.
  • Your expression, put in a text box, displays "2017-W16" which is correct.

You are talking about script code. When did you last do your reload?

More information about how QV calculates week numbers and how you can redefine this method can be found in this great HIC treatment: Redefining the Week Numbers

Best,

Peter

MVP
MVP

Re: how to calculate the week number from a date?

Also note that there is an optional timer mode argument to Today() function, with a default value.

Depending on your test scenario, you could very well get an old date of week 16 returned.

If you want to get the date at script run, use Today(1) in your script statement.

ananyaghosh
Contributor III

Re: how to calculate the week number from a date?

Hi ,

Through week() function I am getting the correct result:

Let vPreviousWeekID = Year(today(1))& '-W' & (week(today(1))-1);

Let vCurrentWeekID = Year(today(1))& '-W' & (week(today(1)));

and it gives me the below results:

Previous WeekID = 2017-W16

current week id = 2017-W17

Please let me know if I am wrong.

Thanks,

Sandip

ananyaghosh
Contributor III

Re: how to calculate the week number from a date?

Hi ,

Through week() function I am getting the correct result:

Let vPreviousWeekID = Year(today(1))& '-W' & (week(today(1))-1);

Let vCurrentWeekID = Year(today(1))& '-W' & (week(today(1)));

and it gives me the below results:

Previous WeekID = 2017-W16

current week id = 2017-W17

Please let me know if I am wrong.

Thanks,

Sandip

Re: how to calculate the week number from a date?

Looks ok to me. Does the weekname() variant return the same values now? It really should...

Re: how to calculate the week number from a date?

May be use this?

=Replace(WeekName(Today()),'/', '-W')

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
ananyaghosh
Contributor III

Re: how to calculate the week number from a date?

Hi,

You are just copying my code and just pasting it?

try it first and you will see that only W16 is coming and it is wrong.

Thanks,

Sandip

Re: how to calculate the week number from a date?

Strange, I've removed -1 from your example. When snap comes to the picture that mean We are tested. Check from your end

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)