@CalcMgrDateDiff and @CalcMgrGetDatePart

@CalcMgrDateDiff:

Syntax:

@CalcMgrDateDiff(fromDate, toDate, date_part)

date_part options:

• "day"
• "month"
• "week"
• "year"

Example(s):

VAR V_Start = 20160701;
VAR V_End = 20170630;
VAR V_Diff = 0;

V_Diff = @CalcMgrDateDiff(V_Start, V_End, "day");

Returns 364

V_Diff = @CalcMgrDateDiff(V_Start, V_End, "month");

Returns 11

V_Diff = @CalcMgrDateDiff(V_Start, V_End, "week");

Returns 52

V_Diff = @CalcMgrDateDiff(V_Start, V_End, @NAME("year"));

Returns 1

Note that when there exists dimension "Year" you may need to put the date_part inside "@NAME" as you see in the last example.



@CalcMgrGetDatePart:

Syntax:

@CalcMgrGetDatePart(date,date_part_ex)

date_part options:

• "dayofmonth"
• "dayofyear"
• "month"
• "weekofmonth"
• "weekofyear"
• "year

Example(s):

VAR V_Start = 20160701;
VAR V_Num = 0;

V_Num = @CalcMgrGetDatePart (V_Start, @NAME("year"));
 
Returns 2016

V_Num = @CalcMgrGetDatePart (V_Start, "dayofmonth");

Returns 1

"BegBalance" = @CalcMgrGetDatePart (V_Start, "dayofyear");

Returns 183

V_Num = @CalcMgrGetDatePart (V_Start, "month");

Returns 7

V_Num = @CalcMgrGetDatePart (V_Start, "weekofmonth");

Returns 1

V_Num = @CalcMgrGetDatePart (V_Start, "weekofyear");

Returns 27

Understanding @IDESCENDANTS function

It is always good to know as how certain function works so that we can get desired, appropriate and expected results.

@IDESCENDANTS ("Zone"):

@IDESCENDANTS function populates all descendants including the specified member however there is a way by which it retrieves the data.

The following screenshot shows how @IDESCENDANTS populates members. Sequence indicate the order in which it populates the members during calculation.

Also, note that this is same as @IDESCENDANTS ("Zone",0)

@IDESCENDANTS ("Zone",-1):

Using negative integer for example -1, system will fetch / populate all members from level 1 upto the selected member. In short, it will exclude level zeros.

See the screenshot below:

@IDESCENDANTS ("East",3):

When you provide a positive number it takes as generation number relative to hierarchy and not the selected member.

So based on the example shown in the screenshot, system will fetch all generation 3 members along with the selected members. 

Note that if the selected member's generation number is less than or equal to the parameter then system will only fetch the specified member.

For example, for @IDESCENDANTS("ENT_E5",2), system will only fetch "ENT_E5" member as generation number of "ENT_E5" is 3 which is less than 2 (parameter).