Show last 4 or 5 weeks of a month

[anjali0108]

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 :

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

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.

[sarvesh]

Hi.. Anjali,

You mean you want only weekly reports not monthly right.

Regard's

Sarvesh Srivastava

[anjali0108]

Hi Sarvesh,

Yes,I want a weekly report only but only the weeks of last month should come.It can be either 4 weeks or 5 weeks depending on the last month.

And it should be in the desired format that I have shown above.

Thanks..

[Anil_Babu_Samineni]

Is there any chance to share the application to work on it due to you are hiding few points over here

[sarvesh]

Hi..

You can perform this task by creating a function to calculate weeks.

[sasikanth]

HI,

Try this

--> to Change Weekstart  (0=Mon,1=tue, 2=Wed ,,, 6=sun)

Ex: Weekstart(Today(),0,1) --> from Tuesday.

SET VWeekStart=1;

vFirstWeek=  Weekstart(Monthstart(max(Date),-1), 0 ,$(VWeekStart))

vLastWeek=  Weekend(MonthEnd(max(Date),-1), 0 ,$(VWeekStart))

DIM:

Calculate MonthWeek at script level. ( Week start should be from Tuesday)

Exp:

Sum({<TranDate={">= $(=vFirstWeek) <= $(=vLastWeek)" }>}Sales)

Please check date foramts.