9 Replies Latest reply: Apr 17, 2012 9:10 AM by Jagan Nalla RSS

    Need help to create aggregation

      I have the following tables

       

      Calendar(Date, Year, Month)

      Work ([Work ID], [Employee ID], Date, [Billable Hours], [Total Hours])

       

      The Billing % is calculated as following : Sum ([Billable Hours])/Sum ([Total Hours])

       

      How can I create expressions that count how many employees have a Billing % in the 90-100 range? The table below visualizes what I am trying to achieve. The table should recalculate values based on the Calendar selections.

       

       

      Billing %

      distribution / employee

      Jan
      Feb
      Mar
      90-100%87436
      80-90%325454
      70-80%62312
      0-70%567

       

      Message was edited by: mikaelstoor Added Calendar table. Changed Month attribute in Work table to Date.

        • Need help to create aggregation
          Jagan Nalla

          Hello,

           

          Use the below code in application.

           

          Main1:

          LOAD EmployeeID,

               BillableHours,

               Month

               TotalHours

          FROM

          Test.xlsx

          (ooxml, embedded labels, table is Sheet1);

           

          Res_Main1:

          LOAD

              EmployeeID,TempBilling,

              If(TempBilling>0 and TempBilling <70,'0-70%',

                  If(TempBilling>=70 and TempBilling <80,'70-80%',

                      if(TempBilling>=80 and TempBilling <90,'80-90%',

                          If(TempBilling>=90 and TempBilling <100,'90-100%')))) as Billing;

          LOAD EmployeeID,Sum(BillableHours)/Sum(TotalHours) as TempBilling

          Resident Main1

          Group By EmployeeID;

           

          - Take a dimension as Billing in straight table and expression as Count(EmployeeID).

          - But according the data as shown by you month wise, So you need create 12 expressions Jan,Feb,Mar.....Dec

           

          For Jan Exp -> Count({<Month={"Jan"}>}EmployeeID)

          For Feb Exp -> Count({<Month={"Feb"}>}EmployeeID)

          .

          .

          Soon

            • Need help to create aggregation

              I realized, when I tried your solution, that I had over-simplified the problem. In reality I have a calender dimension. The table should calculate the Billing% ranges based on the selection in the Year list box.

               

              Calender(Date, Month, Year)

              Work ([Work ID], [Employee ID], Date, [Billable Hours], [Total Hours])

               

              Your example calculates a grand total Billing%, not Billing% per year. I tried to introduce Year and month attributes in the "Res_Main1" attribute, but that leads to having two calendars in the document. I would like to avoid that. Any ideas how to solve the problem?

               


            • Need help to create aggregation
              Iyyappan v

              Hi,

               

                   Use the below expression you will get the count of employee in range '90-100%'

               

              = Count({$<[Billing %]= {'90-100%'}>}([Billable Hours]/[Total Hours]))

               

              Regards,

              Iyyappan

                • Need help to create aggregation

                  Iyyappan V wrote:

                   

                  = Count({$<[Billing %]= {'90-100%'}>}([Billable Hours]/[Total Hours]))

                   

                  It looks like your expression is based on the pre-aggregated table that Jagan Nalla suggested. Do you know how to create an expression without using a pre-aggregated table?

                    • Need help to create aggregation
                      Jagan Nalla

                      I didn't get what your trying to say. Can you explain little bit briefly.

                        • Need help to create aggregation

                          I updated the original description of the problem. I made a mistake by not including the Calendar table in the first version. Your load script creates a table that aggregates all values, but instead it should group the values by Year and Month. As far as I understand, I can't add a Year and Month attribute to the "Res_Main1" table you created. I would get two different Year and Month dimensions. In Calendar table, and in Res_Main1 table. The Calendar table is the master calendar for the document. When I select Year=2011 from Calendar it should show Billing % calculations for 2011, but it should also filter all other graphs by 2011. Therefore I can't add a GROUP BY Year and Month to Res_Main1. It would solve the problem in this simplistic example, but it would introduce duplicate Year and Month dimensions in the real world application. Sorry for any confusion I have created by changing the original description of the problem.

                            • Need help to create aggregation
                              Jagan Nalla

                              You have two tables i.e, Calender and Work.

                               

                              In both tables you have a common field Date. It makes the link between two tables. When you select year 2011 from calender definetly it will effect the other charts also. What is the issue then?

                               

                              Sorry if i'm not clear. Otherwise show me application with sample data. What is the output you want from that data?

                                • Need help to create aggregation

                                  Thanks a lot, you have helped me to find the solution. I interpreted your solution that I need to create a third table, called Res_Main1. The association between that table and Work table was not clear to me. Finally, I understood that I had to use a set expression in order to get the calculations correct. Here's the code I ended up with.

                                   

                                  LOAD

                                      [Employee ID],

                                      TempBilling,

                                      BillingRangeYear ,

                                      BillingRangeMonth ,   

                                      If(TempBilling < 0.3, 1, 0) AS BillingRange0_30,

                                      If(TempBilling>=0.3 and TempBilling < 0.5, 1, 0) AS BillingRange30_50,

                                      If(TempBilling>=0.5 and TempBilling < 0.7, 1, 0) AS BillingRange50_70,

                                      If(TempBilling>=0.7 and TempBilling < 0.8, 1, 0) AS BillingRange70_80,

                                      If(TempBilling>=0.8 and TempBilling < 0.9, 1, 0) AS BillingRange80_90,

                                      If(TempBilling>=0.9 , 1, 0) AS BillingRange90_100;

                                  LOAD [Employee ID],Sum ([Billable Hours])/Sum ([Total Hours]) as TempBilling,     Year([Date]) as BillingRangeYear,    Month([Date]) as BillingRangeMonth

                                   

                                  Resident Work

                                  Group By [Employee ID], Year([Date]), Month([Date]);

                                   

                                   

                                  I used the following expression to get the year filter to work.

                                   

                                  Sum({$<[BillingRangeYear] ={$(=GetFieldSelections([Year]))}>} BillingRange90_100)