1 Reply Latest reply on Apr 20, 2012 11:20 AM by Alex Kerin

How can I make a calculation that changes depending upon the date hierarchy?

I am attempting to normalize employee hours in a workbook.

If an employee worked more than 40 hours in a given week I want to have a field that "normalizes" the hours worked while still maintaining another field with the actual hours.

I got through the normalization calculation by using

if attr([RD Budget Region]) = "Israel" and total(sum([Quantity])) > 42.5

then

sum([Quantity])/total(sum([Quantity]))*42.5

elseif total(sum([Quantity])) > 40

then

sum([Quantity])/total(sum([Quantity]))*40

else

sum([Quantity])

end

This calculation works when I am drilled in at the "week level" on the date dimension that I am using on my columns shelf but I would like to "normalize" to 160 hours when I am looking at months, and 2000 hours for a year.

Is there any way to have the calculation behave differently based upon the level that I have drilled into within my date hierarchy?

• 1. Re: How can I make a calculation that changes depending upon the date hierarchy?

The first thing that comes to mind is to use a parameter to change time periods instead of a drill down- you would then have a calc that does date truncs to create the depth you want in the chart. In the calculation above you can use the parameter to change the values you use to normalize.

I guess it's possible you could also do something funky with trying to check what level of drill down you are at, but I can't think how that would look.