Categories
Create a Logical Column to Sum one column for distinct values in another column
I have a column A, that I want to sum, but since the rows repeat, I only want to sum the values for the distinct values of column B.
Said another way - In the table, there may be many rows for a given value of B, but I only want to sum the value in A for distinct values of B.
I am certain that the value of A will not be different for any given value of B.
Example table:
| Column A | Column B | Column C |
|---|---|---|
| 20 | abc | other interesting stuff |
| 20 | abc | more interesting stuff |
| 30 | def | here |
| 30 | def | is |
| 30 | def | some |
| 30 | def | too |
So, I want a logical column that will total "50" for this data.... the 20 plus the 30.
I cannot find a function that will allow me to specify this in a logical column in the repository. Any ideas?
Thanks,
Scott
Answers
-
SUM of LAST() grouped by Column B
0 -
I can't find any syntax that supports those functions when building a logical column in the Business Model.
Closest I can get is this:
sum(last("SSA BI Warehouse"."BI Dim Course Sections View"."Class Section Capacity" ))
I'm not sure how to add in this part:
group by "SSA BI Warehouse"."BI Dim Course Sections View"."Class Number"
0 -
sum(last("SSA BI Warehouse"."BI Dim Course Sections View"."Class Section Capacity" by "SSA BI Warehouse"."BI Dim Course Sections View"."Class Number"))
This might help explain it a bit: http://www.wegobeyond.co.uk/blog/entry/aggregation-based-on-dimensions-in-obiee.html
0 -
Thomas,
I think that article is referring to a way to provide different aggregation for different hierarchies. I don't need more than one method.
I've tried to place that code into the Expression Builder for a logical column, but it fails with the following message:
"[nQSError: 27002] Near <by>: Syntax error [nQSError: 26012] ."
So, it does not like the use of "by" here. I've tried in the column source for a logical column as well as in the aggregation formula for the regular column.
Version 12.2.1 of the BI Admin tool, if that helps.
0 -
Hi,
I might be misunderstanding your requirement, if so please post some brief illustrative data, but have you thought of pinning a copy of a base measure to the dimension hierarchy represented by B - "A Aggregated by B". You could create an alternative drill path for this purpose if one does not exist.
Hope this helps?
Robert.
0 -
Here are more specifics of the problem.
I have a table with one row per "class". In it, I have a column for capacity of that class. So, it is often something between 20 and 200 depending.
I have another table with one row per enrollment in classes (so, one person might have many rows in this table... likewise, any one class will be represented with multiple rows in this table).
So, I am counting the enrollments and want to compare to capacity. When joining the two tables, I don't want to sum the capacity from every row since that would be adding duplicates.
Not sure if that scenario helps or muddles it.
For any set of rows, I want to do exactly what the first responder wrote.... sum the last of capacity by section. That would do it, but I cannot figure out how to tell that to obiee. in either a logical column, or in any of the other features with dimensions
Thanks for any help you can provide!
Scott
0 -
I stand by my original answer, provided you have decent dimension hierarchies then you can use them to say that another fact only relates to it at total level, or by a specific key level. You do this by going into the LTS and making sure that the dimension and fact have the correct settings.
If you have ever seen OBIA then Oracle solved the same problem by joining pretty much everything to everything, but with total being the level that the facts are set to where the join to the dimension in question is spurious.
0 -
So, the result you are looking for would be something like this?
Capacity | Enrollments | Available
Class A 20 13 7
Class B 30 25 5
---------------------------------------
50 38 12
I still think Thomas' solution should work and it is the best solution IMO.
Alternatively, you could model this having a 'Capacity' Fact and an 'Enrollment' Fact and put the measures together over the conformed dimension 'Class'
0 -
I am still struggling with this.
I am trying to establish this in the setup of the Business Model and maybe thats the wrong place... but thats where I am.
I am trying to provide a measure in the repository that SUMS as its normal aggregate function across any of the dimensions, BUT, it must only sum for rows with distinct values of another column (the ID column).
Examples: Show the sum total of a class capacity measure when joined with enrollments in that class. The resulting table of data would contain one row per enrollment, which would mean the class capacity is repeated many times.
I have a hierarchy setup for class sections. I'm still not clear on how to tie this capacity measure to that hierarchy (at what level, the leaf, or grand total) and which aggregation measure to specify (last, or sum).
I will continue to try variations of those. Thank you for all of your ideas, I wish I understood this a little more deeply.
Scott
0 -
You are in the right place in the business model.
What I am trying to tell you is that you can take your base measure, create a copy, and drag it to associate it with a specific hierarchical level in the dimension hierarchy.
So, for your value that has duplicate IDs, create a dimension hierarchy that includes that in one of its levels, with that value as the key.
Associating the cloned measure with that level will then tell OBIEE that the measure is invariant at that level.
The keys are the key in OBIEE, by a country mile they are the most important, fundamental concept which will bite you if you do not get your head around it, but which can also be greatly used to your advantage once you do.
Hope I have clarified your waters rather than muddying them. See the below if not...
0