Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anjali0108
Partner - Creator III
Partner - Creator III

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
Creator III
Creator III

Hi.. Anjali,

You mean you want only weekly reports not monthly right.

Regard's

Sarvesh Srivastava

anjali0108
Partner - Creator III
Partner - Creator III
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sarvesh
Creator III
Creator III

Hi..

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

sasikanth
Master
Master

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.