Hi!
I have a calendar:
SCRPT_WEEK =if( num(WeekDay(date(today()))) <6 , Year(Today()) * 100 + Week(today()-7), Year(Today()) * 100 + Week(today()))
WeekStart= if( num(WeekDay(date(today()))) <6, (Year(Today()-84)) * 100 + Week(today()-84), (Year(Today()-77)) * 100 + Week(today()-77))
WeekStartN= $(WeekStart) - 3
WeekStartPG= if( num(WeekDay(date(today()))) <6, (Year(Today()-84)-1) * 100 + Week(today()-84), (Year(Today()-77)-1) * 100 + Week(today()-77))
WeekStartPGN = $(WeekStartPG) -3
WeekEnd= if( num(WeekDay(date(today()))) <6 , Year(Today()) * 100 + Week(today()-7), Year(Today()) * 100 + Week(today()))
WeekEndN= $(WeekEnd) -2
WeekEndPG= if( num(WeekDay(date(today()))) <6 , (Year(Today())-1) * 100 + Week(today()-7), (Year(Today())-1) * 100 + Week(today()))
WeekEndPGN = $(WeekEndPG) -3
Week2017=if( num(WeekDay(date(today()))) <6 , Year(Today()) * 100 + Week(today()-7), Year(Today()) * 100 + Week(today()))
Календарь:
LOAD
text('1' & WEEK_ID) as %КалендарьПериод
,YEAR_ID as Год
,left(WEEK_ID, 4) & '/' & right(WEEK_ID, 2) as НеделяГод
,text(WEEK_ID) as Неделя
,WEEK_ID as %НеделяГод
,date(makeweekdate(YEAR_ID,WEEK_YEAR_NUMBER),'YYYYMM') as DATE1
,WEEK_YEAR_NUMBER as %НеделяНомер
From $(SCRPT_CalendarSourse)\weeks.qvd (qvd)
WHERE (WEEK_ID >= $(WeekStartN) AND WEEK_ID<=$(WeekEndN)) OR (WEEK_ID >= $(WeekStartPGN) AND WEEK_ID <= $(WeekEndPGN)) ;
//
Смещение:
LOAD
%КалендарьПериод
,text('1'&Неделя) as %ТипПериод
,0 as ?Смещение
RESIDENT Календарь;
Concatenate (Смещение)
LOAD Distinct
%КалендарьПериод
,text('1'& (Год-1) * 100 + Right(Неделя,2)) as %ТипПериод ///та же неделя год назад
,1 as ?Смещение
RESIDENT Календарь ;
////////
And I have YTD\QTD for months
tmp_dates:
Load
date(date#(mid(%КалендарьПериод,2),'YYYYMM'),'YYYYMM') as DATE1
Resident Календарь
tmp_dates2:
Load
date(YearStart(DATE1),'YYYYMM') as YTD_START
,date(DATE1,'YYYYMM') as YTD_END
,date(AddYears(YearStart(DATE1),-1),'YYYYMM') as YTD_START_LY
,date(AddYears(DATE1,-1),'YYYYMM') as YTD_END_LY
,date(QuarterStart(DATE1),'YYYYMM') as QTD_START
,date(DATE1,'YYYYMM') as QTD_END
,date(AddYears(QuarterStart(DATE1),-1),'YYYYMM')as QTD_START_LY
,date(AddYears(DATE1,-1),'YYYYMM') as QTD_END_LY
Resident tmp_dates;
tmp_dates2:
Load
date(YearStart(DATE1),'YYYYMM') as YTD_START
,date(DATE1,'YYYYMM') as YTD_END
,date(AddYears(YearStart(DATE1),-1),'YYYYMM') as YTD_START_LY
,date(AddYears(DATE1,-1),'YYYYMM') as YTD_END_LY
,date(QuarterStart(DATE1),'YYYYMM') as QTD_START
,date(DATE1,'YYYYMM') as QTD_END
,date(AddYears(QuarterStart(DATE1),-1),'YYYYMM')as QTD_START_LY
,date(AddYears(DATE1,-1),'YYYYMM') as QTD_END_LY
Resident tmp_dates;
tmp_dates3:
IntervalMatch (DATE1) Load YTD_START,YTD_END Resident tmp_dates2;
tmp_dates3_LY:
IntervalMatch (DATE1) Load YTD_START_LY,YTD_END_LY Resident tmp_dates2;
tmp_dates4:
IntervalMatch (DATE1) Load QTD_START,QTD_END Resident tmp_dates2;
tmp_dates4_LY:
IntervalMatch (DATE1) Load QTD_START_LY,QTD_END_LY Resident tmp_dates2;
Concatenate(Смещение)
LOAD Distinct
text('1' & YTD_END) as %КалендарьПериод,
text('1' & DATE1) as %ТипПериод,
10 as ?Смещение
Resident tmp_dates3;
Concatenate(Смещение)
LOAD Distinct
text('1' & Date(AddYears(YTD_END_LY,+1),'YYYYMM')) as %КалендарьПериод,
text('1' & DATE1) as %ТипПериод,
11 as ?Смещение
Resident tmp_dates3_LY;
////для расчета прироста лфл г-г
Concatenate(Смещение)
LOAD Distinct
text('1' & Date(AddYears(YTD_END_LY,+2),'YYYYMM')) as %КалендарьПериод,
text('1' & DATE1) as %ТипПериод,
12 as ?Смещение
Resident tmp_dates3_LY;
drop table tmp_dates;
drop table tmp_dates2;
drop table tmp_dates3;
drop table tmp_dates3_LY;
have can I make other for weeks?
