Create a Logical Column to Sum one column for distinct values in another column

user11079646

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.

Thomas Dodds

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.

asim cholas

Not a simple sum

sum(capacity by 1sttableterm_id,1sttableclass_id)

Thomas Dodds

capacity is an 'inventory measure' and currently is an attribute in his dimension ... he's got to get it to a fact table first ...

user11079646

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?

asim cholas

Did you try in answers?

Thomas Dodds

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).