topic Need help to create aggregation in QlikView

Need help to create aggregation

Mon, 16 Apr 2012 11:11:04 GMT

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.

Need help to create aggregation

jagannalla — Mon, 16 Apr 2012 12:05:28 GMT

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

v_iyyappan — Mon, 16 Apr 2012 12:46:57 GMT

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

Tue, 17 Apr 2012 04:46:18 GMT

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

Tue, 17 Apr 2012 05:12:11 GMT

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

jagannalla — Tue, 17 Apr 2012 07:50:59 GMT

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

Need help to create aggregation

Tue, 17 Apr 2012 10:42:47 GMT

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

jagannalla — Tue, 17 Apr 2012 11:29:54 GMT

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

Tue, 17 Apr 2012 12:58:36 GMT

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)

Need help to create aggregation

jagannalla — Tue, 17 Apr 2012 13:10:17 GMT

That's well buddy..