1 Reply Latest reply on Mar 16, 2016 7:04 AM by Ramin Melikov

    Need Help with an LOD Calculations and the Entire Calculation

    Ramin Melikov

      Hi,

       

      I am working with a report and I need some help as my calculation isn't coming up correctly.

       

      First I have the following functions that you might find useful:

       

      [YTD]:

      [Scheduled Date] <= [Report Date]
      AND
      [Scheduled Date] >= DATEADD('month', -MONTH([Report Date]), [Report Date] + 1)
      

       

      [FTE Calculation]:

      IF 
          YEAR([Scheduled Date]) % 4 <> 0
      THEN 
          IF 
              MONTH([Scheduled Date]) = 1 OR MONTH([Scheduled Date]) = 3 OR MONTH([Scheduled Date]) = 5 OR MONTH([Scheduled Date]) = 7 OR MONTH([Scheduled Date]) = 8 OR MONTH([Scheduled Date]) = 10 OR MONTH([Scheduled Date]) = 12
          THEN 
              176.6575
          ELSEIF 
              MONTH([Scheduled Date]) = 4 OR MONTH([Scheduled Date]) = 6 OR MONTH([Scheduled Date]) = 9 OR MONTH([Scheduled Date]) = 11
          THEN
              170.9589
          ELSE 
              159.5619
          END
      ELSE
          IF 
              MONTH([Scheduled Date]) = 1 OR MONTH([Scheduled Date]) = 3 OR MONTH([Scheduled Date]) = 5 OR MONTH([Scheduled Date]) = 7 OR MONTH([Scheduled Date]) = 8 OR MONTH([Scheduled Date]) = 10 OR MONTH([Scheduled Date]) = 12
          THEN 
              176.8525
          ELSEIF 
              MONTH([Scheduled Date]) = 4 OR MONTH([Scheduled Date]) = 6 OR MONTH([Scheduled Date]) = 9 OR MONTH([Scheduled Date]) = 11
          THEN
              171.1475
          ELSE 
              165.4425
          END
      END
      

       

      I then have a measure called [Hours] and a dimension [Directors]. The hours for YTD is 28303. And YTD FTE Calculation is (you can see from above function) (176.8525+165.4425) = 342.3. What I need to show is 28303/342.3=82.7 broken down by Directors.

       

      So, I first created this

       

      {INCLUDE : SUM(IF [YTD] THEN [Hours] END)}
      

       

      This gives hours for the year (28303)

       

      Then, I created this

       

      {FIXED [MY] : AVG(IF [YTD] THEN [FTE Calculation] END)}
      

       

      This gives a YTD FTE calculation (342.3)

       

      And when I put these together into this

       

      {INCLUDE : SUM(IF [YTD] THEN [Hours] END)} / {FIXED [MY] : AVG(IF [YTD] THEN [FTE Calculation] END)}
      

       

      It gives 331 instead of 82.7.

       

      Can somebody help me with this, please?

       

      Thanks,

       

      Ramin