This article is intended to show users how to perform calculations that are not explicit or easy to perform using the standard Math Toolbox Functions.

Q: How do you calculate (count) the number of values in a WIMS variable Month-to-Date?

A: The answer involves a complex combination of functions, including one "Advanced Math Toolbox" function.  Here is how to do it:

V6 = GETSUM(IF(ISBLANK(C1),0,1),BOM TO #MM/DD/YY)

Quick translation:
V6 = SUM( # Times Variable 1 is not blank) from the Begining OF the Month to Today

Explained:
The GETSUM advanced math toolbox will sum the 1's and 0's from the IF statement from BOM (Begining of Month) TO #MM/DD/YY, which is the current date being calculated (see Date Literals for more information on that).  The IF statement checks to see if the variable C1 (variable 1) is blank or not. If not, then we count it.

NOTE: We can manipulate the Date Literals to Get counts from the begining of the quarter (BOQ) to today, or any other date range you may need.

In Spread Reports, we have a function called GNOS which is specifically designed for this purpose, but if you need it in Math Toolbox (calculated variable), use the above equation.

 

Q: How do you calculate a value only on the last day of the month?

A:  Use TODAYIF and Date Literal of #MM/LL where MM means any month and LL means last day of the month.   

=TODAYIF(#MM/LL,MOV365(C1),BLANK) - Will calculate the 365 day moving average for variable 1 on the last day of each month