#Error in Webi
I have a requirement to show a Department's employee counts at the end of each month for a given year.
Now, for each employee, every time there is a change in his/her location/job title, there is a new entry/row created in the database. Each entry has a different Start and End Date every-time there is a change. So, if I need to get the entry for Dec 31, I need to search for the record/row where Dec 31 falls between the Start and the End Date.
To achieve this in Webi, I have created a variable with the following formula -
Dec = If(ToDate("12/31/2014";"mm/dd/yy") > [Start Date] AND ToDate("12/31/2014";"mm/dd/yy") < [End Date]) Then 1 Else 0.
But, when I drag the variable into the report, I am getting "#ERROR". Also, using the variable as a filter in the table does not show any values for 1 or 0. The table has columns for "Employee Type" and "Employee Count".
Could anyone please help me resolve this issue?