Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
aveeeeeee7en
Valued Contributor III

Week Analysis...............!!

Hi All

1.  I want Week categorization for every Month as Week 1, Week 2,...............

2.  Week should be from Monday to Sunday.

3.  The week should start only from 1st Monday coming in every Month.

Look at my Requirement below:

December 2014:
01-07  -  Week 1
08-14  -  Week 2
15-21  -  Week 3
22-28  -  Week 4
29-04  -  Week 5

January 2015:
05-11  -  Week 1
12-18  -  Week 2
19-25  -  Week 3
26-01  -  Week 4


February 2015:
02-08  -  Week 1
09-15  -  Week 2
16-22  -  Week 3
23-01  -  Week 4

March 2015:
02-08  -  Week 1
09-15  -  Week 2
16-22  -  Week 3
23-29  -  Week 4
30-05  -  Week 5


April 2015:
06-12  -  Week 1
13-19  -  Week 2
20-26  -  Week 3
27-03  -  Week 4

Regards

Av7eN

Tags (1)
1 Solution

Accepted Solutions

Re: Week Analysis...............!!

Hi, PFA.

Also, as we are transforming real dates, for current vs prev month can be easy creating a related field in number format to use in set analysis. Something like Year(WeekStart(Date))*12+Month(WeekStart(Date))-1

8 Replies

Re: Week Analysis...............!!

Hi, seems working with this script:

Main:

LOAD *, 'Week ' & Ceil(Day(WeekStart(New_Date))/7) as Week;

LOAD

Date(MakeDate(2014,12,1)+RecNo()-1,'DD/MM/YYYY') AS New_Date

AutoGenerate(151);

Not applicable

Re: Week Analysis...............!!

Hi Avee,

please look into this blog it is helpful to you....

Redefining the Week Numbers

aveeeeeee7en
Valued Contributor III

Re: Week Analysis...............!!

Hi

Thanks for the reply.

The problem with your code is that it is bound to Particular Month only.

example: For December 2014 Month,  The Week 5 should consider all the below Dates:

29 December, 30 December, 31 December, 1 January, 2 January, 3 January & 4 January

29-04  -  Week 5


But as per your Logic the Week 5 2015 year is showing only 3 Dates i.e., 29 Dec, 30 Dec & 31 Dec

Regards

Av7eN


Re: Week Analysis...............!!

Hi, I see all dates as per requirement, try it.

Re: Week Analysis...............!!

Also, if you want month and year related to the start of the week, you should create those field based on weekstart:

Month: Month(WeekStart(New_Date)) as Month

Year: Year(WeekStart(New_Date)) as Year

aveeeeeee7en
Valued Contributor III

Re: Week Analysis...............!!

Dear Sir

Your Logic is Working perfectly but I want the highlighted cases to be Fall under December Month.

My Final Requirement is to show Current Month vs. Last Month Week on Week Analysis..............!!

PFB:

Week Issue.png

PFA.

Regards

Av7eN

Re: Week Analysis...............!!

Hi, PFA.

Also, as we are transforming real dates, for current vs prev month can be easy creating a related field in number format to use in set analysis. Something like Year(WeekStart(Date))*12+Month(WeekStart(Date))-1

aveeeeeee7en
Valued Contributor III

Re: Week Analysis...............!!

Thank You Sir

Conceptual but great.

Regards

Av7eN

Community Browser