Skip to Content

Below are the list of Formulae used to display the Calendar Day Prompt values in the Webi Report Headings or Header Labels.

1. Getting Start date from Date prompt: Start Date=FormatDate(ToDate(Replace(Left(Replace(UserResponse(“Calendar Day”);” “;”  “);10);” “;””);”MM/dd/yyyy”);”dd.MM.yyyy”)

2. Getting End date from Date prompt: End Date=FormatDate(ToDate(Replace(Substr(Replace(UserResponse(“Calendar Day”);” “;”  “);Pos(Replace(UserResponse(“Calendar Day”);” “;”  “);”;”)+1;10);” “;””);”mm/dd/yyyy”);””)

3. Getting Month Name from Date prompt: Month Name=FormatDate(ToDate(Replace(Substr(Replace(UserResponse(“Calendar Day”);” “;”  “);Pos(Replace(UserResponse(“Calendar Day”);” “;”  “);”;”)+1;10);” “;””);”MM/dd/yyyy”);”MMMM”)

4. Getting Month Value from Date prompt: Month Number=FormatDate(ToDate(Replace(Substr(Replace(UserResponse(“Calendar Day”);” “;”  “);Pos(Replace(UserResponse(“Calendar Day”);” “;”  “);”;”)+1;10);” “;””);”MM/dd/yyyy”);”MM”)

5. Getting Year Value from Date prompt: Year=FormatDate(ToDate(Replace(Substr(Replace(UserResponse(“Calendar Day”);” “;”  “);Pos(Replace(UserResponse(“Calendar Day”);” “;”  “);”;”)+1;10);” “;””);”MM/dd/yyyy”);”yyyy”)

6. Getting Last Year from above formula: LY=FormatNumber((ToNumber([Year])-1);”####”)

7. Getting Before Last Year from above year formula: BLY=FormatNumber((ToNumber([Year])-2);”####”)

8. Getting Start Date from Month prompt: Start Date=FormatDate(ToDate(Replace(Replace(Left(UserResponse(“Calendar Day”);9);” 1″;””);” “;””);”MM/dd/yyyy”);”dd.MM.yyyy”)

9. Getting Month Value from Month prompt: Month Number=Left(UserResponse(“ZMONTH_VAR”);2)

10. Getting Year Value from month prompt: Year=UserResponse(“ZMONTH_VAR”)

11. Getting Fiscal Year Value from above formula: Fiscal Year=If [month] inlist(“January”;”February”;March) Then formatnumber((ToNumber([Year])-1);”##”) Else [Year]

12. Getting Last Day of Month: Last Day Of Month=If [Month Number] InList (“01″;”03″;”05″;”07″;”08″;”10″;”12”) Then “31” ElseIf [Month Number] InList (“02”) Then “28” Else “30”

To report this post you need to login first.


You must be Logged on to comment or reply to a post.

  1. Former Member

    Hello Nirupama,

    Very usefull keep it up…. ..:)

    Getting Last Day of Month: your formula will failed when it comes to Feb month in Leap Year.


Leave a Reply