Help with Cross Table

[former_member277624]

Hi

I am trying to produce a summary table to show the totals against a Business Unit and Area based on the number of people who have been off sick - categorised by the length of time they have been off sick in months.

I have attached screen shots to show what the data looks like and how I want it to appear.

Can anyone help, I think I need guidance on calculation contexts!

Will

sap-webi-cross-table.png

[ayman_salem · Accepted solution]

Define the following variables:

Months Open: =ToNumber(DatesBetween([Start Date Absence];CurrentDate();MonthPeriod))

Note: Type is "Dimension"

Count_0M: =Count([Months Open]; All) Where ( [Months Open] = 0) ForEach ([Business Area])

Count_1M: =Count([Months Open]; All) Where ( [Months Open] = 1) ForEach ([Business Area])

Count_Over_1M: =Count([Months Open]; All) Where ( [Months Open] >1) ForEach ([Business Area])

...

Hope this will help you

[former_member277624 · Accepted solution]

Thank you for your help.

Months open is working for me now. However, when I create the variables such as Count_0M, I get an error message.. The expression or sub-expression at position 48 in the '=' function uses an invalid data type. (IES 10037)

It doesn't appear to recognise the number 0 (zero)

Any ideas?

Thanks

Will

[ayman_salem · Accepted solution]

actually there should be no error.

Can you take a screenshot for your variable?

Workaround: Use <1 insteadof = 0

[former_member277624 · Accepted solution]

Hi - I tried to amend the variable as you advised but this is the error I get. it seems that it isn't recognising the 1 as a number perhaps?

Thanks

Will

[former_member277624 · Accepted solution]

Thanks, I got it to work 🙂