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

      Hi,

       

      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:

       

      Variables:

      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 :

      Capture2.PNG

      Note:

      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 ::

      Capture3.PNG

       

      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.