5 Replies Latest reply: Feb 20, 2017 3:24 AM by sasi k RSS

    Show last 4 or 5 weeks of a month

    Anjali Ahlawat



      I have a requirement where I have to show 4 or 5 weeks of last month.If I see my monthly report on 17th Feb(let's say) then I should see the data for the weeks of January.


      Week starts from Thursday to Friday in the account that I am handling.


      Now the problem is,I was showing last 5 weeks irrespective of the month.


      And I am using below variables and dimensions:



      vFirstDateOfLast5Weeks    =timestamp(weekstart(today()-28))

      vLastDateOfLast5Weeks     =timestamp(weekend(today()))


      Calculated  dimension:

      =if([Common Date]>=vFirstDateOfLast5Weeks and  [Common Date]<=vLastDateOfCurrentWeek ,CommonMonth ) & ' ' &


      if([Common Date]>=vFirstDateOfLast5Weeks and [Common Date]<=timestamp(vFirstDateOfLast5Weeks+7),'Week 1',

      if([Common Date]>timestamp(vFirstDateOfLast5Weeks+7) and [Common Date]<=timestamp(vFirstDateOfLast5Weeks+14),'Week 2',

      if([Common Date]>timestamp(vFirstDateOfLast5Weeks+14) and [Common Date]<=timestamp(vFirstDateOfLast5Weeks+21),'Week3',

      if([Common Date]>timestamp(vFirstDateOfLast5Weeks+21) and [Common Date]<=timestamp(vFirstDateOfLast5Weeks+28),'Week4',

      if([Common Date]>timestamp(vFirstDateOfLast5Weeks+28) and [Common Date]<=timestamp(vFirstDateOfLast5Weeks+35),'Week5','Others')))))

      Below is the chart :



      1. The weeks are not coming in a sorted way.

      2.Client do not want to see Feb weeks if February is the current month going on.

      3.Client wants to see either 4 or 5 weeks of last month only.


      My requirement is :if I am seeing the reports In March then my weeks formation for the month of Feb should be ::



      Week 1: 03-feb to 09-feb

      Week 2: 10-feb to 16-feb

      Week 3: 17-feb to 23-feb

      Week 4: 24-feb to 2-march if data is there...


      Sorry for the complex explanation.But help if you understand the requirement.