-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathForecast Sampler Load Script.qvs
executable file
·82 lines (71 loc) · 3.95 KB
/
Forecast Sampler Load Script.qvs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
set vMeasure = 'sum([Actual])';
set vPeriod = '[Transaction Date.Fiscal.Month]';
set vLastMonth = month(today());
let vLastMonthInt = 1*month(today());
set vTrend = 'linest_m(total aggr(if($(vMeasure),$(vMeasure)),$(vPeriod)),$(vPeriod))*$(vPeriod) +linest_b(total aggr(if($(vMeasure),$(vMeasure)),$(vPeriod)),$(vPeriod))';
//Linear forecast
set vForecast = 'if(only({1}$(vPeriod))<=$(vLastMonthInt),null,linest_m(total aggr(if($(vPeriod)<=$(vLastMonthInt),$(vMeasure)),$(vPeriod)),$(vPeriod))*only({1}$(vPeriod))+linest_b(total aggr(if($(vPeriod)<=$(vLastMonthInt),$(vMeasure)),$(vPeriod)),$(vPeriod)))';
set vMeasForecast = 'if(only({1}$(vPeriod))<=$(vLastMonthInt),$(vMeasure),$(vForecast))';
//Average forecast (all time)
set vAvgForecast = 'if(only({1}$(vPeriod))<=$(vLastMonthInt),null,avg(total aggr(if($(vPeriod)<=$(vLastMonthInt),$(vMeasure)),$(vPeriod))))';
set vMeasAvgForecast = 'if(only({1}$(vPeriod))<=$(vLastMonthInt),$(vMeasure),avg(total aggr(if($(vPeriod)<=$(vLastMonthInt),$(vMeasure)),$(vPeriod))))';
//Average forecast (3 period rolling)
set v3pdAvgForecast = 'if(only({1}$(vPeriod))<=$(vLastMonthInt),null,rangeavg(Above($(vMeasure),(only({1}$(vPeriod))-$(vLastMonthInt)),3)))';
set vMeas3pdAvgForecast = 'if(only({1}$(vPeriod))<=$(vLastMonthInt),$(vMeasure),rangeavg (Above($(vMeasure),(only({1}$(vPeriod))-$(vLastMonthInt)),3)))';
//Color expression for bar charts
set vColorExpression = 'if ($(vPeriod)<=$(vLastMonthInt),blue(),red())';
//Sample data, for the sampler
Data:
LOAD
MakeDate(year(today()),ceil(Rand1*$(vLastMonth)),1) as [Transaction Date],
Pick(Ceil(6*Rand1),'C1','C2','C3','C4','C5','C6') as Customer,
Pick(Ceil(6*Rand2),'a','b','c','d','e','f') as Product,
Pick(Ceil(5*Rand()),'R1','R2','R3','R4','R5') as Region,
Round(1000*Rand()*Rand()*Rand1) as Actual,
Round( 10*Rand()*Rand()*Rand2) as Units,
Round(Rand()*Rand1,0.00001) as Margin;
Load
Rand() as Rand1,
Rand() as Rand2,
IterNo() as TransLineID,
RecNo() as TransID
Autogenerate 3000
While Rand()<=0.5 or IterNo()=1;
;
Calendar:
LOAD
MakeDate(year(today()),recno(),1) as [Transaction Date]
AUTOGENERATE 12
;
[Fiscal]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified')
;
DERIVE FIELDS FROM FIELDS [Transaction Date] USING [Fiscal];