QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Highlighted Partner

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

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.

2 Replies MVP

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

Check if the attached is helpful

Table:

AutoNumber(Week&MonthYear) as WeekNo;

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

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

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