Skip to Content
Author's profile photo Former Member

Webi Formulae for Calendar Day Prompt

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”);”dd.mm.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”

Assigned tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      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.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Yes ur wright 🙂 for that again one more condition we need to include for Leap Year Feb month.

      Author's profile photo Former Member
      Former Member

      Hi Nirupa,

      Good Post for beginners.

      To get the last day of month we can use function LastDayofMonth(date).

      Author's profile photo Kranthi Kumar Sreeram
      Kranthi Kumar Sreeram

      thanks. and good effort.