1 Reply Latest reply: Jul 14, 2011 12:58 PM by yaman1510 RSS

    Help needed in writing this expression!!

      Hello All,

       

      I am unsure on how to calculte the turnover percentage from the below table. Below is an example of how my data look like in database. Everyday a snapshot of data is taken and is stored in a data warehouse. Each date when the snapshot is taken is stored in the effective date column.

       

      Emplid
      TerminationDate
      Gender
      EffectiveDate
      14/25/2006M9/28/2006
      2-M9/28/2006
      38/28/2005F9/28/2006
      4-F9/28/2006
      5-M9/28/2006
      14/25/2006M10/28/2006
      2-M10/28/2006
      38/28/2005F10/28/2006
      49/28/2006F10/28/2006
      5-M10/28/2006
      6-F10/28/2006



      10/28/2007



      10/28/2007



      10/28/2007



      10/28/2007



      10/28/2007



      10/28/2007



      10/28/2007

       

      As, you can see on each date the data keeps on changing (new employees are added and some eployees left the company. I have to use :

       

      Dimension as FY-06, FY-07, etc...where FY-06 corresponds to a specific date range..eg from ,23sep,05 to 28 , oct, 06.

      second dimension as Gender.

       

      Expression is something I am facing problem. I have a created a daterange using interval match for FY-06, FY-07 etc.

       

      Turover for 06 would be:

       

      %= count(distinct(all employees in Fy -06, where TermDate is not NULL) /

      ((count(for the date range for FY-06(the total employees based on male & female seperately)/ total count distinct date (date when snapshots are taken in database) for FY-06))

       

      this is for fy-06. So the formula should be generalized and should calcuate for each year and display it on bar graph.

       

      I used something like this:

       

      count(isnull(termdate=0,Emplid))/((count(Total<daterange, gender>, emplid)/ X)

       

      X- should vary distinctly. Like suppose if we have 300 days of which we are taking data in account for FY-06, it should return 300 and so on for FY-07, FY-08, FY-09 etc.

      how to add a denominator that can calculate count of total distinct date for each year??

       

      this is for fy-06. So the formula should be generalized and should calcuate for each year and display it on bar graph.

       

      I am facing hard time in finding a solution to this. Please help me out.

       

      Thanks .