topic Re: Aggregate sum in QlikView

Aggregate sum

wossenhh — Mon, 14 Jul 2014 18:26:28 GMT

Hi,

Based on the attached table 1, I want to create table 2 in QlikView. Can any body give me a hint?

Thanks,

Wossen

I have two tables as it shown on the attachment (Plan Time.xls). One is for Actual time table that shows hours for different employee who spent their time on different clients. The second one is the Plan time table. I was trying to show on QlikView exactly what it has shown as Plan Vs. Actual table. I use the expression 'Aggr(Sum(Hours), Client='A' , Employee)' for Actual time and 'Aggr(Sum(A), Employee' for Plan time and this works well for some of employee but not for all. For example for employee Peter I get 90 Hrs under the 'Actual A' column which is correct, but for the rest of employee it gives me the total value of 642 instead of zero. And the result is the same under 'Plan A' column as well. I get 100 Hrs for Peter and instead of zero for the rest of employees, it gives me the total value of 690.

I used Employee for Dimensions

Can anybody please advise me what I missed here?

Thanks,

Wossen

Re: Aggregate sum

lironbaram — Mon, 14 Jul 2014 18:53:03 GMT

hi basically you look at a pivot table

have a look at the attach example

Re: Aggregate sum

TKendrick20 — Mon, 14 Jul 2014 18:58:40 GMT

Create a new Pivot Table chart. Use Employee and Client as your Dimensions (in that order). Use SUM(Hours) as your expression. Then press OK. Expand the Client tab in your chart and drag the Client title so that it is shown horizontally. That should get you pretty close.

Re: Aggregate sum

its_anandrjs — Mon, 14 Jul 2014 19:03:24 GMT

Another way of doing the same is

T1:

LOAD * INLINE [

CLIENT, EMPLOYEE, HOURS

A, Peter, 10

B, Bill, 12

B, Rick, 15

C, Judy, 32

C, Betty, 80

C, Betty, 77

C, Betty, 22

C, Bill, 90

D, Ana, 44

E, Ryan, 65

];

Final:

LOAD

CLIENT,

Sum( if(CLIENT='A', HOURS)) as TotA,

Sum( if(CLIENT='B', HOURS)) as TotB,

Sum( if(CLIENT='C', HOURS)) as TotC,

Sum( if(CLIENT='D', HOURS)) as TotD,

Sum( if(CLIENT='E', HOURS)) as TotE

Resident T1

Group By CLIENT;

And then plot values of the Final table as in any table box or straight tables.

Re: Aggregate sum

wossenhh — Tue, 15 Jul 2014 17:51:41 GMT

Thanks Anand! It works

Re: Aggregate sum

wossenhh — Tue, 15 Jul 2014 17:53:47 GMT

Hi Tim,

Thanks for your reply and I like the trick to drag the Client title to show horizontally, even though it is hard to get it easily . It is great tips!