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..