4 Weeks in a month with Varying number of Days in weeks

[amithmurali]

Hi Guys,

I have a requirement for my client and i am not able to create a logic for the below scenario.

All the months are divided into 4 weeks. Lets take example of January. It has 31 Days.

So Week1 will be Jan 1 to Jan 7,

     Week 2 will be Jan 8 to Jan 15

     Week 3 will be Jan 16 to Jan 23

    Week 4 will be Jan 24 to Jan 31.

Here we can see that inorder to divide Jan month into 4 weeks . They are considering 8 days for a week from the 2nd week onwards.

Similarly for a month if they have 30 days, they will consider 8 days for a week from 3rd week onwards.

Please look at the table below which shows the number of days in a week and week number.

Number of days in month	Week 1	Week 2	Week 3	Week 4
28	7 Days	7 Days	7 Days	7 Days
30	7 Days	7 Days	8 Days	8 Days
31	7 Days	8 Days	8 Days	8 Days
29	7 Days	7 Days	7 Days	8 Days

They need to show the Total revenue for the current week.

for example, lets assume maximum available date is 22nd of January. then it falls under Week 3. So i need to show total revenue from Jan 16 to Jan 22. 

Thanks in Advance.

QlikView App Development‌ QlikView Forums & Resources‌

[sunny_talwar]

Check if the attached is helpful

Table:

LOAD *,

AutoNumber(Week&MonthYear) as WeekNo;

LOAD *,

If(Day(Date) < 8, 'Week 1',

If(Day(MonthEnd(Date)) = 31, If(Day(Date) < 16, 'Week 2', If(Day(Date) < 24, 'Week 3', 'Week 4')),

If(Day(MonthEnd(Date)) = 30, If(Day(Date) < 15, 'Week 2', If(Day(Date) < 23, 'Week 3', 'Week 4')),

If(Day(Date) < 15, 'Week 2', If(Day(Date) < 22, 'Week 3', 'Week 4'))))) as Week,

Year(Date) as Year,

MonthName(Date) as MonthYear;

LOAD Date(MakeDate(2015, 1, 1) + IterNo() - 1) as Date,

Ceil(Rand() * 100) * 100 as Revenue

AutoGenerate 1

While MakeDate(2015, 1, 1) + IterNo() - 1 <= MakeDate(2018, 1, 1);

Created a flag for the weeks based on the number of days and then created a continuous counting WeekNo field... and then use this expression

=Sum({<Date = {"$(='<=' & Max(Date))"}, WeekNo = {"$(=Max(WeekNo))"}>}Revenue)

[MarcoWedel]

Hi,

another solution might be:

Match(-1,Day(Date)<8, Day(Date)<15-(Day(MonthEnd(Date))>30), Day(Date)<22-(Day(MonthEnd(Date))>29)-(Day(MonthEnd(Date))>30),-1) as Week

hope this helps

regards

Marco