Need help to create aggregation

[#1]

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%	87	43	6
80-90%	32	54	54
70-80%	6	23	12
0-70%	5	6	7

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

[jagannalla · Accepted solution]

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

[v_iyyappan]

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

[#4]

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?

[#5]

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?

[jagannalla]

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

[#7]

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.

[jagannalla]

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?

[#9]

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)

[jagannalla]

That's well buddy..