Distinct values in a dimension variable not showing up on aggregation

[Former Member]

I want to show a report that identifies whether an employee has submitted time within the Closing date of any given month or not. The Closing date is basically the last week ending date of the month (last Saturday of the month).

I've managed to get what I want at an employee and timesheet level as shown below. However, when I try to summarize I get a #MULTIVALUE error.

I expect to see 2 records in the 2nd table one with Compliance Status (Monthly) = "Submitted Before Close" with Timesheet# = 3 and with "Submitted Before Close" and Timesheet# = 1.

Why is the "Compliance Status (Monthly)" column showing a #MULTIVALUE error?

Formula for the Variables used

[Compliance Status (Monthly)] =If [Time Submission Gap (Monthly)] >= 1 Then "Submitted After Close"  Else "Submitted Before Close"

[Time Submission Gap (Monthly)]=DaysBetween(ToDate([Closing Week of Month] In ([Timesheet Month]);"yyyy-mm-dd");ToDate([Submitted Date];"yyyy-mm-dd"))

[Closing Week of Month]=Max([Timesheet End Date] ForEach ([Timesheet Month])) In ([Timesheet Month])

Note: The last 2 variables got converted to measure automatically.

[kohesco]

Hi,

Please check out doc: http://scn.sap.com/docs/DOC-54825

I hope it helps you

Kind Regards

Koen

[sateesh_kumar1]

Hi Vivek,

What you are expecting to see ?

is it count([Submitted Before Close/ Submitted After Close])

or details of Submitted Before Close/ Submitted After Close

For count :

[Compliance Status (Monthly)] =

If [Time Submission Gap (Monthly)] >= 1 Then "Submitted After Close"  Else "Submitted Before Close"

count of employees Submitted After Close

=count([Compliance Status (Monthly)] where ([Compliance Status (Monthly)] ="Submitted After Close") foreach([Employee]))

count of employees Submitted  Before Close

=count([Compliance Status (Monthly)] where ([Compliance Status (Monthly)] ="Submitted Before Close") foreach([Employee]))

For Details

Restrict (Filter ) table with [Compliance Status (Monthly)] ="Submitted After Close" 

or

[Compliance Status (Monthly)] ="Submitted Before Close"