topic Problem with AGGR Function and Totals in QlikView

Problem with AGGR Function and Totals

Wed, 03 Nov 2010 18:15:10 GMT

For some reason my AGGR expressions are not working for Totals. (See Below)

Any help as to how i can get this to work would be appreciated.

The expression i am using is

AGGR

(NODISTINCT SUM(RegCnt),ReportSuperGrouping ,ReportGrouping,YearQuarter)

		YearQuarter	2010Q1	2010Q1	2010Q1
ReportSuper Grouping (HW)	ReportGrouping (HW)	SuperCategory (Services)	AttachRate	Attach	RegUnits
SuperGroup1	RG1000	Category1	1.65%	3	182
SuperGroup1	RG1000	Category2	43.41%	79	182
SuperGroup1	RG1000	Category3	12.64%	23	182
SuperGroup1	RG1000	Total	49.45%	90	182
SuperGroup1	RG2000	Category2	47.06%	32	68
SuperGroup1	RG2000	Category3	17.65%	12	68
SuperGroup1	RG2000	Total	52.94%	36	68
SuperGroup1	RG3000	Category1	3.57%	1	28
SuperGroup1	RG3000	Category2	25.00%	7	28
SuperGroup1	RG3000	Category3	10.71%	3	28
SuperGroup1	RG3000	Total	28.57%	8	28
SuperGroup1	Total		-	134	-
Total			-	134	-

Thanks

Graham

Problem with AGGR Function and Totals

Wed, 03 Nov 2010 19:38:31 GMT

It's because in your totals, you have multiple ReportGrouping values and QlikView can't decide which one to use. In your total, you have 182, 68 and 28, which one should it display?

If you want the total of those three values, then I think you just need to put a Sum around the Aggr():

Sum(AGGR(NODISTINCT SUM(RegCnt),ReportSuperGrouping ,ReportGrouping,YearQuarter))

Problem with AGGR Function and Totals

Wed, 03 Nov 2010 20:59:35 GMT

Thanks for the response - I suspected that totals where not being calculated because the function was not sufficient to allow them to be calculated.

Unfortunately your suggestion did not work. Totals were being calculated but it caused all the RegUnits to show incorrectly. I think this is the right track so any further suggestions would be appreciated.

		YearQuarter	2010Q1	2010Q1	2010Q1	2010Q1
ReportSuperGrouping	ReportGrouping	SuperCategory	AttachRate	Attach	RegUnits	NewRegUnits
SuperGroup1	RG1000	Category1	1.65%	3	182	546
SuperGroup1	RG1000	Category2	43.41%	79	182	4,732
SuperGroup1	RG1000	Category3	12.64%	23	182	2,184
SuperGroup1	RG1000	Total	49.45%	90	182	5,096
SuperGroup1	RG2000	Category2	47.06%	32	68	748
SuperGroup1	RG2000	Category3	17.65%	12	68	476
SuperGroup1	RG2000	Total	52.94%	36	68	1,020
SuperGroup1	RG3000	Category1	3.57%	1	28	28
SuperGroup1	RG3000	Category2	25.00%	7	28	112
SuperGroup1	RG3000	Category3	10.71%	3	28	28
SuperGroup1	RG3000	Total	28.57%	8	28	112
SuperGroup1	Total		-	134	-	6,228
Total			-	134	-	6,228

Problem with AGGR Function and Totals

pover — Wed, 03 Nov 2010 21:06:50 GMT

Instead of the aggr function try the following expression:

Sum(Total <ReportSuperGrouping ,ReportGrouping,YearQuarter> RegCnt)

Regards.

Problem with AGGR Function and Totals

Wed, 03 Nov 2010 21:21:27 GMT

Many Thanks Karl. This is looking very close now. I just need to figure out why the RegUnits and NewRegUnits do not match.

		YearQuarter	2010Q1	2010Q1	2010Q1	2010Q1
ReportSuperGrouping	ReportGrouping	SuperCategory	AttachRate	Attach	RegUnits	NewRegUnits
SuperGroup1	RG1000	Category1	1.65%	3	182	161
SuperGroup1	RG1000	Category2	43.41%	79	182	161
SuperGroup1	RG1000	Category3	12.64%	23	182	161
SuperGroup1	RG1000	Total	49.45%	90	182	161
SuperGroup1	RG2000	Category2	47.06%	32	68	62
SuperGroup1	RG2000	Category3	17.65%	12	68	62
SuperGroup1	RG2000	Total	52.94%	36	68	62
SuperGroup1	RG3000	Category1	3.57%	1	28	27
SuperGroup1	RG3000	Category2	25.00%	7	28	27
SuperGroup1	RG3000	Category3	10.71%	3	28	27
SuperGroup1	RG3000	Total	28.57%	8	28	27
SuperGroup1	Total		-	134	-	250
Total			-	134	-	250

Problem with AGGR Function and Totals

pover — Wed, 03 Nov 2010 21:47:31 GMT

Have you checked with the source data to confirm which is the correct number?

Regards.

Problem with AGGR Function and Totals

lucarizziero — Thu, 01 Sep 2011 13:59:02 GMT

Hi,

Did you were able to solve the situation ?

I have a similar one.

Thanks