2 Replies Latest reply: Dec 3, 2017 7:36 PM by Marco Wedel RSS

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

    Amith Murali

      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

        • Re: 4 Weeks in a month with Varying number of Days in weeks
          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)

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

            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