Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
amithmurali
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
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