Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
anjali0108
Partner
Partner

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

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.