Syntax to use lookup functions in a single conditional expression in the filter. (HY000) Code: 42047

Question

I am trying to get a single number using the FILTER() Function in Oracle Analytics Answers. The current expression I have is as follows: FILTER("Workforce Event Facts"."Department Change Count" USING "Gregorian Calendar"."Year Name" = ValueOf(CURRENT_YEAR) AND "Employment"."User Person Type Name" IN ('Employee', 'Employee_Expat', 'Employee_Expat - Host') AND " Department (Previous) Hierarchy"."Previous LOB Name" = 'Corporate Affairs 11111') I need to add this additional criteria to the statement so that I can get one returned number: AND "Department (Previous) Hierarchy"."Previous LOB Name" <> "Department Hierarchy"."LOB Name" What I want my result to look like this: Can some one please tell me how to correct my syntax? or Create something similar with different logic? This doesn't seem to work: FILTER("Workforce Event Facts"."Department Change Count" USING "Gregorian Calendar"."Year Name" = ValueOf(CURRENT_YEAR) AND "Employment"."User Person Type Name" IN ('Employee', 'Employee_Expat', 'Employee_Expat - Host') AND " Department (Previous) Hierarchy"."Previous LOB Name" = 'Corporate Affairs 11111' AND "Department (Previous) Hierarchy"."Previous LOB Name" <> "Department Hierarchy"."LOB Name") I get an error when I try to do the above. Any help regarding my issue would be much appreciated. Thanks in Advance.

mrmmickle1 · Answer

Thanks so much!!  Not sure how I missed that

Gianni Ceresa · Answer

Did you try by adding this instead:

"Department Hierarchy"."LOB Name" <> 'Corporate Affairs 11111' ?

As you set your "Department (Previous) Hierarchy"."Previous LOB Name" value in the FILTER just hardcode the same value once more

If you still have an error, would be good to post the error (as it could come from your model but no way to guess without).