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”