0 Replies Latest reply on Jun 16, 2015 2:21 PM by 2657149

    filter function







      I am sourcing the data from the Essbase cube.

      We have to build a report which shows the data for Dept 1 and Dept 2 and the difference of the data

      We are having two measures:revenue and volume on the report.

      However, only revenue is associated between Dept 1 and Dept 2.
      I am using union reports for dept 1 and dept 2 and difference using a calculated item.


      The Attendee Revenue record is associated only to Dept 1 and the records in green are associated to Dept 2 and the last line in red is the difference

      We are calculating the volume for Dept 2, I have to find the sum of the volume for Reg_status in ('Attended','Cancelled') as volume for the record for Dept 1(For Attendee Revenue).

      The formula that I am using as part of column formula for Actual Volume for Dept 1 is as  below:

      Filter(COUNT(DISTINCT "RegistrationCode"."Gen2,RegistrationCode - Default")  using "View"."Gen2,View - Default" = 'YTD' AND "Period"."Gen4,Period - Default" = 'December' AND "Scenario"."Gen2,Scenario - Default"='Actuals' AND "RegistrationStatus"."Gen2,RegistrationStatus - Default" IN ('Attended','Cancelled'))


      I see that all filters are getting applied except the Registration Status.


      There is no difference in the measure value even when I put 'XYZ' instead of ('Attended','Cancelled').

      I cannot put this filter at report level because other data will be restricted for Dept 1.

      Any ideas why I am facing this issue with filter function please?


      Thanks in advance..