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
- Get the current date in a format we can guarantee getting the days part of the date
- Format it so we can get the days of the date by formatting it with the days first
- 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))
Sharing is Caring. Thanks for reading and Sharing ❤
