#Error in Webi

[former_member672670]

Hi Guys,

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?

[amitrathi239]

Hi,

Use this.

Dec = If(ToDate("12/31/2014";"mm/dd/yyyy") > [Start Date] AND ToDate("12/31/2014";"mm/dd/yyyy") < [End Date]) Then 1 Else 0.

Amit

[former_member672670]

i filtered one particular employee using your formula, but it produced a start date of 1/26/14 and end date of 3/14/14 which is not the correct result as 12/31/14 > 3/14/14. For some reason, the formula produces incorrect result when the AND operator is used.

[saurabh_sonawane]

Please provide a screen shot