Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 213 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Create a Logical Column to Sum one column for distinct values in another column
Answers
-
No, I need enrolled summed for every row and capacity summed only for unique classes.
If I were to query ALL of the data (no filters), grouped by Term/Dept, I would want to see something like this:
Term_ID Dept Enrolled Capacity 1176 ART 0 30 1179 ART 4 60 1179 BIOL 0 40 If I were to remove Dept, to just see the numbers at the term level, I would want to see:
Term_ID Enrolled Capacity 1176 0 30 1179 4 100 So, I can get the enrolled count easy - its a simple measure that just sums.
Capacity cannot have a simple sum on it since the value is repeated when > 1 person is enrolled in a class.
0 -
make a logical fact out of dim_sections
you care trying to make 'capacity' a measure and you've got it in your dimension (it's both so you need both structures)
you have 2 facts and one dimension.
0 -
Not a simple sum
sum(capacity by 1sttableterm_id,1sttableclass_id)
0 -
capacity is an 'inventory measure' and currently is an attribute in his dimension ... he's got to get it to a fact table first ...
0 -
Asim,by
That is exactly the formula I want - but OBIEE won't let me put that syntax in the expression for a logical column. I get the following error:
"[nQSError: 27002] Near <by>: Syntax error [nQSError: 26012] ."
Thomas,
I created a new logical table called "fact_sections" and added in the logical columns for the key and the "capacity", my measure. Initial reports are looking like it is aggregating correctly. I will finish up my testing and post back.
So.... it seems like measures will naturally just aggregate at the granularity of the fact table they are in - is that true?
0 -
Did you try in answers?
0 -
Welcome to dimensional modeling ... facts (measures) are what is aggregated BY dimensions (attributes)
Sales (aggregated fact) by employee (dimensional attribute) by month (dimensional attribute)
You set the aggregation rule (default is usually SUM) ... but in the case of an inventory, where you can not add across time, the aggregation rules needs to be set at LAST (most recent count).
0
