Webi Formulae for Calendar Day Prompt

[Former Member]

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"