Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have the date field its start from 12-28-2014 want to set this date as 1st week so on...
while using master calendar i can't achieve this in master calendar.
how can i achieve this below output while using the master calendar.
PFA of QVW.
2015 | 2016 | ||
WEEKNO | DATE | WEEKNO | DATE |
1 | 12/28/2014 | 1 | 12/27/2015 |
2 | 1/4/2015 | 2 | 1/3/2016 |
3 | 1/11/2015 | 3 | 1/10/2016 |
4 | 1/18/2015 | 4 | 1/17/2016 |
5 | 1/25/2015 | 5 | 1/24/2016 |
6 | 2/1/2015 | 6 | 1/31/2016 |
7 | 2/8/2015 | 7 | 2/7/2016 |
8 | 2/15/2015 | 8 | 2/14/2016 |
9 | 2/22/2015 | 9 | 2/21/2016 |
10 | 3/1/2015 | 10 | 2/28/2016 |
11 | 3/8/2015 | 11 | 3/6/2016 |
12 | 3/15/2015 | 12 | 3/13/2016 |
13 | 3/22/2015 | 13 | 3/20/2016 |
14 | 3/29/2015 | 14 | 3/27/2016 |
15 | 4/5/2015 | 15 | 4/3/2016 |
16 | 4/12/2015 | 16 | 4/10/2016 |
17 | 4/19/2015 | 17 | 4/17/2016 |
18 | 4/26/2015 | 18 | 4/24/2016 |
19 | 5/3/2015 | 19 | 5/1/2016 |
20 | 5/10/2015 | 20 | 5/8/2016 |
21 | 5/17/2015 | 21 | 5/15/2016 |
22 | 5/24/2015 | 22 | 5/22/2016 |
23 | 5/31/2015 | 23 | 5/29/2016 |
24 | 6/7/2015 | 24 | 6/5/2016 |
25 | 6/14/2015 | 25 | 6/12/2016 |
26 | 6/21/2015 | 26 | 6/19/2016 |
27 | 6/28/2015 | 27 | 6/26/2016 |
28 | 7/5/2015 | 28 | 7/3/2016 |
29 | 7/12/2015 | 29 | 7/10/2016 |
30 | 7/19/2015 | 30 | 7/17/2016 |
31 | 7/26/2015 | 31 | 7/24/2016 |
32 | 8/2/2015 | 32 | 7/31/2016 |
33 | 8/9/2015 | 33 | 8/7/2016 |
34 | 8/16/2015 | 34 | 8/14/2016 |
35 | 8/23/2015 | 35 | 8/21/2016 |
36 | 8/30/2015 | 36 | 8/28/2016 |
37 | 9/6/2015 | 37 | 9/4/2016 |
38 | 9/13/2015 | 38 | 9/11/2016 |
39 | 9/20/2015 | 39 | 9/18/2016 |
40 | 9/27/2015 | 40 | 9/25/2016 |
41 | 10/4/2015 | 41 | 10/2/2016 |
42 | 10/11/2015 | 42 | 10/9/2016 |
43 | 10/18/2015 | 43 | 10/16/2016 |
44 | 10/25/2015 | 44 | 10/23/2016 |
45 | 11/1/2015 | 45 | 10/30/2016 |
46 | 11/8/2015 | 46 | 11/6/2016 |
47 | 11/15/2015 | 47 | 11/13/2016 |
48 | 11/22/2015 | 48 | 11/20/2016 |
49 | 11/29/2015 | 49 | 11/27/2016 |
50 | 12/6/2015 | 50 | 12/4/2016 |
51 | 12/13/2015 | 51 | 12/11/2016 |
52 | 12/20/2015 | 52 | 12/18/2016 |
53 | 12/25/2016 |
Try this pls.
Let vDate=Date(Date#('12/28/2014','MM/DD/YYYY'),'DD/MM/YYYY');
Calendar:
load Date('$(vDate)'+(RowNo()-1)*7) as Date,
if(mod(RowNo(),53)=0,1,
if(Year(Date('$(vDate)'+(RowNo()-1)*7))=2016,
mod(RowNo(),53)+1,
mod(RowNo(),53)
)
) as WeekNo
autogenerate 105;
Hi Stephen,
Maybe you can find diferent information in:
How to use - Master-Calendar and Date-Values
Regards
Miguel del Valle
where can i put this script inside the master calendar ?
in master calendar only
yeah i cant get , each week have several week stat date
Hi Stephen,
You can try this script as well:
//---------------------------------------------------------------------------
LET vMinDate = 42001; // Start Date
LET vMaxDate = 42729; // End Date
//*****Temp Calendar*****
TempCal:
LOAD
DATE($(vMinDate) + ROWNO() -1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
Calendar:
Load *, AutoNumber(WeekEnd,YearNo) as WeekNo;
Load *, Year(WeekEnd) as YearNo;
Load
*,
Date(WeekEnd(TempDate+1)-1) as WeekEnd
resident TempCal;
Drop Table TempCal;
//---------------------------------------------------------------------------
Please let me know if it helps!
Regards,
Sakir
hi
sakirali
its working fine when we give the min and max date .
actually my start date is 7-26--15 (32nd week)
i want to show this week in selection field of dashboard
if i hardcore the min and max date it take 7-25-15 as 1 st week. but i want this date start as 32nd week of 2015.
Hi,
I think even if you are selecting any random date as your start date, you must keep a certain date as a standard from where the calculation should start.
For example, you can simply write:
LET vStartDate= 42211; // 7/26/2015
LET vMinDate = WeekStart(YearStart(Date(vMinDate)))-1;
and then proceed with the above script.
Hope it helps!
Regards,
Sakir