Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Partner
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. 

Thanks in Advance.

QlikView App DevelopmentQlikView Forums & Resources

2 Replies

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

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)

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