背景:
通过脚本生成数据:
let vMinDate=num(Date('2015-01-01'));
let vMaxDate=num(Date('2018-06-11'));
日历数据:
LOAD
Date(TempDate) as ddate,
num(WeekDay(TempDate)) as 星期数,
Day(TempDate) as 日,
Week(TempDate) as 周,
Num(Month(TempDate)) as 月,
Month(TempDate) as 月份,
Year(TempDate) as 年,
'Q'&Ceil(Month(TempDate) / 3) as 季度;
LOAD
$(vMinDate) + IterNo() - 1 as TempDate
Autogenerate 1 While ($(vMinDate) + IterNo() - 1) <= $(vMaxDate);
STORE 日历数据 into 日历数据.qvd;
DROP Tables 日历数据;
事实表:
LOAD ddate,
num(rand()*100,'#,##0') as sales,
星期数,
日,
周,
月,
月份,
年,
季度
FROM 日历数据.qvd (qvd);
则常见时间偏移公式:
YTD : =sum({ <ddate={'>=$(=YearStart(max(ddate))) <=$(=max(ddate))'}>}sales) 20180101-20180611
LYTD: =sum({ <ddate={'>=$(=YearStart(max(ddate),-1)) <=$(=addyears(max(ddate),-1))'}>}sales) 20170101-20170611
MTD : =sum({ <ddate={'>=$(=monthStart(max(ddate))) <=$(=max(ddate))'}>}sales) 20180601-20180611
LMTD: =sum({ <ddate={'>=$(=monthStart(max(ddate),-1)) <=$(=addmonths(max(ddate),-1))'}>}sales) 20180501-20180511
LYMTD:=sum({<ddate={'>=$(=addyears(monthStart(max(ddate)),-1))<=$(=addyears(max(ddate),-1))'}>}sales) 20170601-20170611: