How to Calculate Number Of Days in a Month or Month To Date (MTD) in WebIntelligence (webi)

0
168
webi dates

We will need to know a couple of useful Webi and Rich Client functions: namely LastDayOfMonth, DayNumberOfMonth , CurrentDate(), ToNumber(), Left()

How to get Number of days in the current Month

For a specified date, the LastDayOfMonth will give you the last day in that month.

so =LastDayOfMonth(CurrentDate()) will give you the last day of the current month then to use this in calculations you must convert it to a number using the DayNumberOfMonth function

=DayNumberOfMonth(LastDayOfMonth(CurrentDate()))

How to get the day of the current day or the MTD value

We need to do three things

  1. Get the current date in a format we can guarantee getting the days part of the date
  2. Format it so we can get the days of the date by formatting it with the days first
  3. convert it to numbers to use it in calculations

Step1

=FormatDate(CurrentDate();”dd/MM/yyyy”)

Step2

=Left(FormatDate(CurrentDate();”dd/MM/yyyy”);2)

Step3

=ToNumber(Left(FormatDate(CurrentDate();”dd/MM/yyyy”);2))

How to a create a partial prorated MTD value

this is done in 2 steps

step1: to get the value for 1 day

=  (VALUE/ Days in Current month)

step2: get the amt for the days so far in the month

=  (VALUE/ Days in Current Month) * Current day

use your VALUE and first part and second part of this article

= (Monthly VALUE / DayNumberOfMonth(LastDayOfMonth(CurrentDate())) ) / ToNumber(Left(FormatDate(CurrentDate();”dd/MM/yyyy”);2))

 

Stephen Choo Quan

Stephen is a double threat holding both SAP business objects certified architect as well as being a certified IBM DB2 Database Developer. read More…