week

[ajsjoshua]

Hi all,

I want to redefine the week.

2014-12-28 should be my week 1.

Sunday is the first day of week.

PFA.

[migueldelval]

Hi Joshua,

Read this article it will helps you.

How to use - Master-Calendar and Date-Values

Regards

Miguel del Valle

[ajsjoshua]

Hi migueldelval

I tried that but couldnt achieve it .

[avinashelite]

check this threads

Redefining the Week Numbers

Redefining the Week Start

[migueldelval]

Hi Joshua,

Send me a sample of data and I try to help you.

Regards

Miguel del Valle

[ajsjoshua]

Hi,

PFA.

[ajsjoshua]

Hi avinashelite

I want only 52 weeks in a year.

My output:

2015	2016
WEEKNO	DATE	WEEKNO	DATE
1	2014-12-28	1	2015-12-27
2	2015-01-04	2	2016-01-03
3	2015-01-11	3	2016-01-10
4	2015-01-18	4	2016-01-17
5	2015-01-25	5	2016-01-24
6	2015-02-01	6	2016-01-31
7	2015-02-08	7	2016-02-07
8	2015-02-15	8	2016-02-14
9	2015-02-22	9	2016-02-21
10	2015-03-01	10	2016-02-28
11	2015-03-08	11	2016-03-06
12	2015-03-15	12	2016-03-13
13	2015-03-22	13	2016-03-20
14	2015-03-29	14	2016-03-27
15	2015-04-05	15	2016-04-03
16	2015-04-12	16	2016-04-10
17	2015-04-19	17	2016-04-17
18	2015-04-26	18	2016-04-24
19	2015-05-03	19	2016-05-01
20	2015-05-10	20	2016-05-08
21	2015-05-17	21	2016-05-15
22	2015-05-24	22	2016-05-22
23	2015-05-31	23	2016-05-29
24	2015-06-07	24	2016-06-05
25	2015-06-14	25	2016-06-12
26	2015-06-21	26	2016-06-19
27	2015-06-28	27	2016-06-26
28	2015-07-05	28	2016-07-03
29	2015-07-12	29	2016-07-10
30	2015-07-19	30	2016-07-17
31	2015-07-26	31	2016-07-24
32	2015-08-02	32	2016-07-31
33	2015-08-09	33	2016-08-07
34	2015-08-16	34	2016-08-14
35	2015-08-23	35	2016-08-21
36	2015-08-30	36	2016-08-28
37	2015-09-06	37	2016-09-04
38	2015-09-13	38	2016-09-11
39	2015-09-20	39	2016-09-18
40	2015-09-27	40	2016-09-25
41	2015-10-04	41	2016-10-02
42	2015-10-11	42	2016-10-09
43	2015-10-18	43	2016-10-16
44	2015-10-25	44	2016-10-23
45	2015-11-01	45	2016-10-30
46	2015-11-08	46	2016-11-06
47	2015-11-15	47	2016-11-13
48	2015-11-22	48	2016-11-20
49	2015-11-29	49	2016-11-27
50	2015-12-06	50	2016-12-04
51	2015-12-13	51	2016-12-11
52	2015-12-20	52	2016-12-18

[migueldelval]

Hi Joshua,

Try this example please and tell me if it´s ok.

Regards

Miguel del Valle

[ajsjoshua]

Himigueldelval

Pls share the script

i am using personal edition.

[migueldelval]

Hi Joshua:

UploadCalesdar:

MinMax:

LOAD

  MIN(InvoiceDate_UP) AS MinDate,

  MAX(InvoiceDate_UP) AS MaxDate

RESIDENT Fact ;

LET vMinDate = NUM(PEEK('MinDate',0,'MinMax'));

LET vMaxDate = NUM(PEEK('MaxDate',0,'MinMax'));

LET vToday = $(vMaxDate);

//*****Temp Calendar*****

TempCal:

LOAD

  DATE($(vMinDate) + ROWNO() -1) AS TempDate

AUTOGENERATE

  $(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

//*****Master Calendar*****

UploadCalendar:

LOAD

TempDate AS InvoiceDate_UP,

//if(Week(TempDate) = 53,'1',Week(TempDate)) as Week_UP,

If(Week(TempDate)<53 and Week(TempDate)<>0,week(TempDate)) AS Week_UP,

YEAR(TempDate) AS Year_UP,

MONTH(TempDate) AS Month_UP,

DAY(TempDate) AS Day_UP,

WEEKDAY(TempDate) AS WeekDay_UP,

WeekStart(TempDate) AS WeekStart_UP,

AutoNumber(YEAR(TempDate) & MONTH(TempDate), 'MonthID') as [MonthID_UP],

AutoNumber(YEAR(TempDate) & Week(TempDate), 'WeekID') as [WeekID_UP],

'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2)))  AS Quarter_UP,

date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))) as YearQuarter_UP,

AutoNumber(date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))),'QuarterID') as QuarterID_UP,

DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS  MonthYear_UP,

WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear_UP,

date(yearstart(TempDate,1,8),'YYYY') AS YearFiscal_UP,

//INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag_UP,

//INYEARTODATE(TempDate,$(vToday),-1,1) * -1 AS LastYTDFlag

INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag_UP,

INYEARTODATE(TempDate,$(vToday),-1,8) * -1 AS LastYTDFlag_UP

//INYEARTODATE(TempDate,TempDate,0,8) * -1 AS CurYTDFlag,

//INYEARTODATE(TempDate,TempDate,-1,8) * -1 AS LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

Regards

Migeul del Valle