Simple Aggr Subtotal problem.

[robinrouleur]

Hi, can anyone shed any light on why the following doesn't give expected results.  My actual requirement is more complex but I created this example to trace the root cause to Aggr not working as I expected.

I have a single table in the model with columns ID, DimA, DimB.  I also load "1 AS IDcount" for summing.   ID is unique but there may be multiple rows with the same DimA and/or DimB. My requirement is to report on DimA based on subtotals of the row counts of values in DimB.  Ie: the number of repeat occurrances of DimB for each DimA.

I created a straight table chart with the single dimension DimA and two expressions.  The first is a control expression and the second tests Aggr against my understanding.  I expected the same result as Aggr creates subtotals for each value of DimB which the outer Sum() adds up, ie the sum of all the component subtotals equals the grand total.

Expression1: Sum(IDcount))

Expression2: Sum( Aggr( Sum(IDcount), DimB ) )

The result is that the Total row at the top of the chart is as expected (ie both expressions the same) but the majority of the aggregations against individual values of DimA are wrong.  What is more puzzling is that when I click on a single row to select a single value of DimA the value of the Aggr expression for that one row becomes correct.

After a bit of experimentaton we found that adding ",DimA" after "DimB" in the above Expression2 gave correct results but it seems kludgy and in my real use-case I can't do this as my DimA is actually a drill-down group.

Can anyone tell me what I am missing?

If my expectatons of Aggr() are incorrect can anyone suggest an alternative?  My core requirement is to report against each unique vaue of DimA the number of unique values of DimB that occur more than once, eg:

Sum( If (Aggr( Sum(IDcount), DimB ) ) > 1, 1, 0)

I hope that makes sense.

Thanks in advance,

Robin

[MK_QSL]

Can you provide few lines of data in excel please?

[robinrouleur]

Hi Manish

This dialog window doesn't appear to give me an option to attach an XL file so here is a paste of both a sample input and the output

Input

ID	DimA	DimB	IDcount
2810	23	1849	1
2851	24	1849	1
4098	28	2710	1
5890	30	3891	1
6065	31	2582	1
6484	24	2582	1
8861	28	5814	1
10616	24	6910	1
12119	28	5499	1
12261	28	5499	1
13288	34	1013	1
13300	34	8777	1
13803	34	9078	1
14038	34	9222	1

Output

DimA	Control Count	Count using Aggr
	14	14
23	1	2
24	3	1
28	4	4
30	1	1
31	1	2
34	4	4

Control Count = Sum(IDcount)

Aggr Count = Sum(Aggr(Sum(IDcount), DimB))

Aggr Count is wrong for DimA=23,24 & 31.

Regards

Robin

[settu_periasamy]

Hi Robin,

try this..

Sum(Aggr(Sum(IDcount), DimA,DimB))

PFA the sample.

[robinrouleur]

Thanks Settu

That works for my simplified example where DimA is fixed but in my real-life use-case DimA is a drill-down group.  Do you know of a solution for this more general case?

[settu_periasamy]

Hi Robin,

I think, it will work on drill-down group also.

Did you tried this in your real scenario?

Edit: find attached the drill-down group example.

[robinrouleur]

It doesn't unfortunately Settu. The reason is that the solution you describe is statically dependent on the dimension being reported against.  If it is just DimA then all good but if it becomes DimC(cyclic) or DimA & DimC (drill-down) then, to follow your logic, we would need to dynamically change the aggr list to "DimB,DimC" in the first case and "DimB,DimA,DimC" in the second.

It is also worth noting that adding dimensions to the aggr list introduces another problem.  It will, in general, change the value in the Totals row depending on the underlying data model.  In this simple example it doesn't but in my real-life use-case it does.

[jonathandienst · Accepted solution]

You can use GetCurrentField() to do this - like this:

     Sum(Aggr(Sum(IDcount), DimB, $(=GetCurrentField(DrillGroup)))

     or

     Sum(Aggr(Sum(IDcount), DimB, $(=GetCurrentField('DrillGroup')))

where DrillGroup is the drill down group name.

[robinrouleur]

Excellent Jonathan, that fixes my main problem, just my totals row to fix now!  Any idea how I can get my total to be the value aggregated against just DimB without the drill group fields?  With the additional aggr dimensions the total is now higher because some records match multple aggregations.

[robinrouleur]

FYI jontydkpi  and in case anyone else has the same problem, I discovered a solution to this based on RowNo()=0 returning true for a chart's Totals row.  Here it is:

If(RowNo()=0,

  Sum(Aggr(Sum(IDcount), DimB),

  Sum(Aggr(Sum(IDcount), DimB, $(=GetCurrentField(DrillGroup)))

)