Set Analysis to aggregate on calculated (or associated) dimension value
I need to create a straight table where the aggregations in one column must be based on a specific/associated dimension, but it's no the dimension 'at that level' in the table. I've created a simple project to illustrate (attached).
For example, consider the following data. Notice we have a Group field by which we want to aggregate the Value field. Each Group has a specific group that we'll want to aggregate in the same row. So for Groups 1A, 1B, 1C... the RefGroup (reference group) group is 1Ref.
In the table below, we have the desired output. So we see the two dimensions (Group and RefGroup). Our first metric (Sum) is just an normal aggregation on the Group. The 2nd metric (Ref Sum) is an aggregation for the RefGroup associated with that Group. So for 1A, 1B, and 1C, it's going to be an aggregation of Group = 1Ref.
In sample data above, the *Ref values are always larger than the other values, but this is fake data, the actual values will vary. And in the final output, we'll exclude the 'Ref' dimensions from Group, so they will not appear. So we'll see rows for 1A, 1B, 1C, 2A, 2B, 3A, 3B, and 3C.
The question is how to create that 2nd metric above (Ref Sum) so that is aggregates just the value where essentially Group=RefGroup.
I've tried varying Set Analysis solutions, but since Set Analysis is not evaluated row-by-row, perhaps it's not possible? I'm hitting a wall on this, and hoping someone can help. Thanks so much for any ideas or insights.
NOTE: If this is not possible via an expression, by plan B is to pre-aggregate values in the model as another table. But before going that route, I'd prefer to create an expression in the chart if possible.
Thanks for the reply. No, that's certainly an option. So I could just create another table with the aggregates (sums) grouped by RefGroup (where Group=RefGroup). For example:
TableRef: LOAD RefGroup, sum(Value) as RefValue Resident Table1 where Group = RefGroup group by RefGroup;
Looks like this is indeed what I'll have to do. But I posted here just because this seemslike something that should be possible at the record-level in a chart, using Set Analysis perhaps. So I just wanted to get the input of folks here to see if I was missing something.
I'm using the SUM in the way I did because for all records where RefGroup="1Ref", I need to get the sum where RefGroup="1Ref" AND Group="1Ref". So I don't want to sum all the 1Ref records, and I don't want the max() cause it won't always be the max.
So all those that are 1A, 1B, and 1C will be 10, because that's the value of the RefGroup ("1Ref") for those records. But it's just 10 in my fake data here. It could be that the 1Ref/1Ref value is 2, for example.
All good suggestions though, and helpful feedback. Thank you again!
I like the ideas offered here on both using a Flag in the model or just JOIN'ing to add the aggregation to the model.
But doing with straight SET ANALYSIS was the initial objective, and you've offered just that solution. I had danced around it, but I was neglecting to use TOTAL <RefGroup> to limit dimensions for the AGGR().