Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Problem with AGGR Function and Totals

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)

YearQuarter2010Q12010Q12010Q1
ReportSuper
Grouping (HW)
ReportGrouping
(HW)
SuperCategory
(Services)
AttachRateAttachRegUnits
SuperGroup1RG1000Category11.65%3182
SuperGroup1RG1000Category243.41%79182
SuperGroup1RG1000Category312.64%23182
SuperGroup1RG1000Total49.45%90182
SuperGroup1RG2000Category247.06%3268
SuperGroup1RG2000Category317.65%1268
SuperGroup1RG2000Total52.94%3668
SuperGroup1RG3000Category13.57%128
SuperGroup1RG3000Category225.00%728
SuperGroup1RG3000Category310.71%328
SuperGroup1RG3000Total28.57%828
SuperGroup1Total-134-
Total-134-


Thanks

Graham

Tags (1)
6 Replies
Not applicable

Problem with AGGR Function and Totals

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))


Not applicable

Problem with AGGR Function and Totals

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.

YearQuarter2010Q12010Q12010Q12010Q1
ReportSuperGroupingReportGroupingSuperCategoryAttachRateAttachRegUnitsNewRegUnits
SuperGroup1RG1000Category11.65%3182546
SuperGroup1RG1000Category243.41%791824,732
SuperGroup1RG1000Category312.64%231822,184
SuperGroup1RG1000Total49.45%901825,096
SuperGroup1RG2000Category247.06%3268748
SuperGroup1RG2000Category317.65%1268476
SuperGroup1RG2000Total52.94%36681,020
SuperGroup1RG3000Category13.57%12828
SuperGroup1RG3000Category225.00%728112
SuperGroup1RG3000Category310.71%32828
SuperGroup1RG3000Total28.57%828112
SuperGroup1Total-134-6,228
Total-134-6,228


Luminary
Luminary

Problem with AGGR Function and Totals

Instead of the aggr function try the following expression:

Sum(Total <ReportSuperGrouping ,ReportGrouping,YearQuarter> RegCnt)
Regards.
Not applicable

Problem with AGGR Function and Totals

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

Luminary
Luminary

Problem with AGGR Function and Totals

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

Regards.

Partner
Partner

Problem with AGGR Function and Totals

Hi,

Did you were able to solve the situation ?

I have a similar one.

Thanks