Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Can you provide few lines of data in excel please?
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
Hi Robin,
try this..
Sum(Aggr(Sum(IDcount), DimA,DimB))
PFA the sample.
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?
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.
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.
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.
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.
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)))
)