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?