topic Re: Sum in pivot table? or what?... in QlikView

Sum in pivot table? or what?...

Sun, 09 Nov 2014 16:41:04 GMT

Hi all.

Here is my problem:

I have the following 2 tables:

ID Join Date Left Date Membership Days (a variable containing the difference between date2 and date1)

111 date11 date21 1000

222 date12 date22 2000

333 date13 date23 1500

ID Start Vacation Date End Vacation Date Vacation Days

111 date111 date211 400

111 date112 date212 50

222 date211 date212 200

333 date311 date312 300

I need to create the following 2 tables:

ID Total Vacation Days

111 450

222 200

333 300

ID Total Active Days

111 550 (=1000-450)

222 1800

333 1200

I tried using variables, pivot tables, sums etc... but couldn't get the result that I needed 😞

Thanks a lot for your help!

Merav.

Re: Sum in pivot table? or what?...

its_anandrjs — Sun, 09 Nov 2014 18:22:48 GMT

Hi,

I suppose it is simple equations load two tables and then have common fields ID and then leave the table as association and load it like

LOAD * INLINE [

ID, Join Date, Left Date, Membership Days

111, date11, date21, 1000

222, date12, date22, 2000

333, date13, date23, 1500

];

LOAD * INLINE [

ID, Start Vacation Date, End Vacation Date, Vacation Days

111, date111, date211, 400

111, date112, date212, 50

222, date211, date212, 200

333, date311, date312, 300

];

An then create the straight table or Pivot table

Chart1

Dim:- ID

Exper:- sum([Vacation Days])

Chart2

Dim:-ID

Exper:- Sum( [Membership Days] ) - sum( [Vacation Days] )

And you get the data and charts as

Regards

Anand

Re: Sum in pivot table? or what?...

Sun, 09 Nov 2014 18:32:12 GMT

above explination is perfect

Re: Sum in pivot table? or what?...

Sun, 09 Nov 2014 18:35:54 GMT

Oh, I feel so stupid... 🙂

Thanks a lot for your detailed help and patience in explaining.