Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
anjali0108
Contributor II

Show last 4 or 5 weeks of a month

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.

5 Replies
sarvesh
Contributor III

Re: Show last 4 or 5 weeks of a month

Hi.. Anjali,

You mean you want only weekly reports not monthly right.

Regard's

Sarvesh Srivastava

anjali0108
Contributor II

Re: Show last 4 or 5 weeks of a month

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

Re: Show last 4 or 5 weeks of a month

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

Life is so rich, and we need to respect to the life !!!
sarvesh
Contributor III

Re: Show last 4 or 5 weeks of a month

Hi..

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

sasikanth
Valued Contributor III

Re: Show last 4 or 5 weeks of a month

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.

Community Browser