QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for
Did you mean:
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

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

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