topic Re: Sum of rows and expression total are different in QlikView

Sum of rows and expression total are different

Wed, 25 Nov 2020 16:16:04 GMT

I have below expression in Bar chart and Straight table...in straight table when I sum the rows I get different numbers and in bar chart I get different numbers however when I do expression total in straight table they match.

How can I do the same sum in bar chart ?

(if(ABRBuildingCode = 'THOM-B5635',22,

IF(Capacity-((count(if(len([EM EmpName])=0,

IF([RoomTypeAssigned]='OFFICE',

UniqueAB2Key)))

count(if(len([EM EmpName])=0,

IF([RoomTypeAssigned]='WRKST',

UniqueAB2Key))))) = 0

or COUNT(distinct (UniqueAB2Key)) =0,

sum([AB1BuildingCpacity(Manual)]),

COUNT(distinct (UniqueAB2Key)))))

Re: Sum of rows and expression total are different

sunny_talwar — Tue, 10 May 2016 14:58:37 GMT

Can you share some screenshots to show the discrepancy?

Re: Sum of rows and expression total are different

Tue, 10 May 2016 15:31:20 GMT

Expression total for Space type Office

Sum of rows for space type office

Re: Sum of rows and expression total are different

sunny_talwar — Tue, 10 May 2016 15:36:53 GMT

How about Add Sum(Aggr()) around the expression?

=Sum(Aggr(

(if(ABRBuildingCode = 'THOM-B5635',22,

IF(Capacity-((count(if(len([EM EmpName])=0,

IF([RoomTypeAssigned]='OFFICE',

UniqueAB2Key)))

count(if(len([EM EmpName])=0,

IF([RoomTypeAssigned]='WRKST',

UniqueAB2Key))))) = 0

or COUNT(distinct (UniqueAB2Key)) =0,

sum([AB1BuildingCpacity(Manual)]),

COUNT(distinct (UniqueAB2Key))))),

Dimensions))

Re: Sum of rows and expression total are different

Tue, 10 May 2016 15:40:48 GMT

Sum(Aggr(

(if(ABRBuildingCode = 'THOM-B5635',22,

IF(Capacity-((count(if(len([EM EmpName])=0,

IF([RoomTypeAssigned]='OFFICE',

UniqueAB2Key)))

count(if(len([EM EmpName])=0,

IF([RoomTypeAssigned]='WRKST',

UniqueAB2Key))))) = 0

or COUNT(distinct (UniqueAB2Key)) =0,

sum([AB1BuildingCpacity(Manual)]),

COUNT(distinct (UniqueAB2Key))))),

ABRUsage))

I tried that..that didnt do any diff

Re: Sum of rows and expression total are different

sunny_talwar — Tue, 10 May 2016 15:42:41 GMT

Are you trying to show 65,229 in the bar chart?

Re: Sum of rows and expression total are different

Tue, 10 May 2016 15:54:43 GMT

Yeah.

Re: Sum of rows and expression total are different

Tue, 10 May 2016 16:14:42 GMT

stalwar1‌Why would I get different counts ?

Re: Sum of rows and expression total are different

sunny_talwar — Tue, 10 May 2016 16:19:57 GMT

I think the issue might be related to the distinct count and/or the if statements. I will have to play around to give the exact answer

Re: Sum of rows and expression total are different

Tue, 10 May 2016 16:22:12 GMT

Ok.Please let me know if you come up wit something.Thanks much.

Re: Sum of rows and expression total are different

sunny_talwar — Tue, 10 May 2016 16:26:05 GMT

Would you be able to provide some sample raw data to test this out?

Re: Sum of rows and expression total are different

Tue, 10 May 2016 18:25:15 GMT

I have 5 files linked together....and the data is huge even for 1 ID...