101tiwihks-51-bypassing-the-interpreter.qvs

// *******************************************************
// ** Data
// *******************************************************

data:
Load
	Num(MakeDate(2020,01) + IterNo())								as %date,
	RowNo() 														as id,
    Pick(Ceil(Mod(RowNo(),4))+1, 'North','West','South','East') 	as region,
    1000 + (Ceil(Rand()* 4)) 										as %product,
    Ceil(Rand() * 25) 												as qty,
    Round(Rand() * .25,0.005) 										as discount
AutoGenerate
	(10000)
While
	iterNo() <= 250;
    

product:
Load
	1000 + RowNo() 					as %product,
	1000 + RowNo() 					as product_id,
    Chr(64+RowNo())					as product,
    Round(Rand() * 100,2.5) 		as price
AutoGenerate
	(4);
    
    
 
dates:
Load
	FieldValue('%date', RowNo()) as %date
AutoGenerate
	(FieldValueCount('%date'));

    
calendar:
Load
    %date 																										as %date,
    Date(%date) 																								as Date,
    Num(%date) 																									as date_num,
	Dual('W'&Num(Week(%date),'00'),Week(%date)) 																as [Week],
    Weekday(%date) 																								as [Day of Week],
	Month(%date) 																								as [Month],
    Num(Month(%date)) 																							as month_num,
    Num(Day(%date)) 																							as [Day of Month],
    Dual(Text(Month(%date)) & '-' & Num(Day(%date),'00'),DayNumberOfYear(%date)) 								as [Month Day],
    Dual(Month(%date)&'-'&Year(%date),Year(%date)*100+Num(Month(%date))) 										as [Month Year],
    AutoNumber(Year("%date")*100+Num(Month(%date)),'month_year') 												as month_year_num,
	Dual('Q'&Num(Ceil(Num(Month(%date))/3)),Num(Ceil(NUM(Month(%date))/3),00)) 									as [Quarter],
    DayNumberOfQuarter(%date) 																					as [Day of Quarter],
	Dual('Q'&Num(Ceil(Num(Month(%date))/3))&'-'&Year(%date),Year(%date)*10+Num(Ceil(NUM(Month(%date))/3),00)) 	as [Quarter Year],
	Year(%date) 																								as [Year],
    Year(%date) 																								as year_num,
    DayNumberOfYear(%date) 																						as [Day of Year]
Resident
	dates
Order by
	%date asc; 

Drop Table dates;

Autonumber %date;

// *******************************************************
// ** UI/UX
// *******************************************************

Set vSetType = 'MTD'; 
Set vSetIgnore = '';


for f = 0 to NoOfFields('calendar') - 1

	Let vFieldName = FieldName($(f)+1,'calendar');
    
    If WildMatch('$(vFieldName)','*_num') = 0 Then
    
    	Let vSetIgnore = vSetIgnore & ',[$(vFieldName)]=';
        
    End If
    
next f;



// Date Set Analysis Variables
Let vSetDay = 'date_num = {"$' & '(=Max(total date_num))"} $(vSetIgnore)';
Let vSetWTD = 'date_num = {">=$' & '(=Num(WeekStart(Max(total date_num)))) <= $' & '(=Max(total date_num))"} $(vSetIgnore)';
Let vSetMTD = 'date_num = {">=$' & '(=Num(MonthStart(Max(total date_num)))) <= $' & '(=Max(total date_num))"} $(vSetIgnore)';
Let vSetQTD = 'date_num = {">=$' & '(=Num(QuarterStart(Max(total date_num)))) <= $' & '(=Max(total date_num))"} $(vSetIgnore)';
Let vSetYTD = 'date_num = {">=$' & '(=Num(YearStart(Max(total date_num)))) <= $' & '(=Max(total date_num))"} $(vSetIgnore)';
Let vSetLast7 = 'date_num = {">=$' & '(=Num(Max(total date_num)-6))) <= $' & '(=Max(total date_num))"} $(vSetIgnore)';
Let vSetLast30 = 'date_num = {">=$' & '(=Num(Max(total date_num)-29))) <= $' & '(=Max(total date_num))"} $(vSetIgnore)';
Let vSetLastRolling13 = 'date_num = {">=$' & '(=Num(MonthStart(Max(total date_num),-12))) <= $' & '(=Max(total date_num))"} $(vSetIgnore)';



period:
Load * Inline [
period
Day
WTD
MTD
QTD
YTD
];


measure:
Load 
	measure,
    Replace(expression,'???','$') as expression
Inline "
measure|expression
Quantity|Sum({<???(vSet???(=Only(period)))>} [qty])
Gross Sales|Sum({<???(vSet???(=Only(period)))>} [qty]*[price])
Net Sales|Sum({<???(vSet???(=Only(period)))>} [qty]*[price]*[discount])
"(delimiter is '|');


// *******************************************************
// ** Quote Examples
// *******************************************************

Let vExample01 = 'This is our example' & chr(39) & 's first go at it';

Let vExample02 = 'This is our example''s second go at it';

Set vExample03 = This example works if your expression doesn't need to evaluate anything;