Custom Functions and Master Calendar

//custom defined functions
 Let fGreaterNumber = 'if(num($2) >= num($1), num($2), num($1))';
 // How to use:
 // =$(fGreaterNumber(30,100)) give you the greater number of the two, which is 100

Let fSmallerNumber = 'if(num($2) <= num($1), num($2), num($1))';
 // How to use:
 // =$(fGreaterNumber('1/1/2016','1/1/2017')) give you 1/1/2016 which is the smaller date (also number)

Let fRemoveDate = 'left($1, Index($1, ''|'')-1)';
 // input: 68332|2016/12/12
 // out put: 68332

// custom function to calculate month difference, can be used in expression too
 SET fMonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))));
 // to use in expression: =$(fMonthDiff(Today(), Today() + 65))

set fRoundToMinuteTime = Time(round(Frac($1),1/24/60),'h:mm');

MinMax: 

LOAD   min(DATE) as MinDate, 

max(DATE) as MaxDate

FROM Table1;

let vMaxDate = num(Peek('MaxDate', 0, 'MinMax'));

let vMinDate = num(Peek('MinDate', 0, 'MinMax'));

let vToday = Date(Peek('MaxDate', 0, 'MinMax'));

Let vCurrentMonth = text(Date(Peek('MaxDate', 0, 'MinMax'), 'MMM'));
 TempCalendar:

Load date($(vMinDate) + rowno() - 1) As TempDate

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

Drop table MinMax;
 MasterCalendar:

LoadTempDate as Date,

1 as Date_Flag,

Day(TempDate) as Day,

Week(TempDate) as Week,

Year(TempDate) as Year,

Month(TempDate) as Month,

Weekday(TempDate) as WeekDay,

WeekStart(TempDate) as WeekStart,

date(WeekEnd(TempDate), 'DDMMM') as WeekEnding,

date(WeekStart(TempDate), 'DDMMM') & '-' & date(WeekEnd(TempDate), 'DDMMM') as WeekName,dual(text(Date(monthstart(TempDate), 'MMM-YY')), $(fMonthDiff($(vMinDate), TempDate))) as MonthYear, // auto assign ranking to month-year text, in expression: max(MonthYear) does return the right value$(fMonthDiff($(vMinDate), TempDate)) as MonthNumber

Resident TempCalendar

Order by TempDate ASC;

drop table TempCalendar;

Table1:
 load *, Date($(fGreaterNumber(START_DT, $(vMinDate)))+IterNo()-1) as DATE
 From Table2
 While IterNo() <= $(fSmallerNumber(END_DT, $(vMaxDate))) - $(fGreaterNumber(START_DT, $(vMinDate))) + 1;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.