topic Re: Week Analysis...............!! in QlikView

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

aveeeeeee7en — Thu, 02 Apr 2015 08:59:19 GMT

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

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

rubenmarin — Thu, 02 Apr 2015 09:22:30 GMT

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);

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

Thu, 02 Apr 2015 09:24:09 GMT

Hi Avee,

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

Redefining the Week Numbers

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

aveeeeeee7en — Thu, 02 Apr 2015 09:32:01 GMT

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...............!!

rubenmarin — Thu, 02 Apr 2015 09:39:32 GMT

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

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

rubenmarin — Thu, 02 Apr 2015 09:49:03 GMT

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

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

aveeeeeee7en — Thu, 02 Apr 2015 10:22:11 GMT

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:

PFA.

Regards

Av7eN

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

rubenmarin — Thu, 02 Apr 2015 10:30:46 GMT

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

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

aveeeeeee7en — Thu, 02 Apr 2015 11:02:30 GMT

Thank You Sir

Conceptual but great.

Regards

Av7eN