Number of weeks in Sales and Budget

[poooja]

Hi Qliks,

My source has sales & budget data so it has order date, Transaction date and budget date. Please help  me with some logic for the below scenario .

1. How to find number of weeks in a month? In script.

2. How to find particular order date(ex: max(order date)) falls in which week of the month.

Thanks!

Pooja

[Shubham_Deshmukh]

To get this, having Master calendar is always great deal.

Create master calender using below script,
MySales:
LOAD myDate,
mySake
FROM
[C:\Users\KT3028\Desktop\masterCalender.xlsx]
(ooxml, embedded labels, table is Sheet1); // this will be your data

Load
date(myDate) as myDate,
year(myDate) as myYear,
'Q' & Ceil(month(myDate)/3) as myQuarter,
Month(myDate) as myMonth,
Day(myDate) as myDay,
Week(myDate) as myWeek;

Load
Date(MinDate + IterNo()-1) as myDate
While(MinDate + IterNo() - 1) <= num(MaxDate);

Load
Min(date(Date#(myDate,'DD-MM-YYYY'))) as MinDate,
Max(date(Date#(myDate,'DD-MM-YYYY'))) as MaxDate
Resident MySales;

Now just use getpossiblecount(myWeek) to get no. of weeks in month.

Elaborate your second requirement, I think that will also get resolve using this.

[poooja]

Hi Shubham,

Thanks for your response. Actually below is my actual requirement.

How to find 'Total Number of weeks in the current month'. Week starts from Monday.If the week has started already it should be considered as same week irrespective of the new month start. If the month starts on middle of the week that should consider as last week of the previous month.I am able to achieve but not as desired.

(Answer is: Jan 5 weeks,Feb 4 weeks,Mar 4 weeks,Apr 5 weeks, May 4 weeks,June 4 weeks, July 5 weeks,Aug 4 weeks,Sep 4 weeks,Oct 5 weeks,Nov 4 weeks,Dec 5 weeks,)

Result what i am getting is : Feb 5 weeks, March 6 weeks but it should be 1 week less because Feb 1 false under last week of January and march 1 falls under last week of Feb.

Thanks!

Pooja