Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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 |
Instead of the aggr function try the following expression:
Sum(Total <ReportSuperGrouping ,ReportGrouping,YearQuarter> RegCnt)
Regards.
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 |
Have you checked with the source data to confirm which is the correct number?
Regards.
Hi,
Did you were able to solve the situation ?
I have a similar one.
Thanks