How to use multiple aggregates In OTBI

Question

Hello,

I am brand new to OTBI, and am having a little bit of difficulty providing the analysis that my business is requesting of me.  The end result should look like this:

I am using the Workforce Management - Assignment Real Time Subject Area for the Vertical (the top level ADMIN), as well as the Department (next level in) and Management Level final row in) and then the Workforce Management - Person Real Time for the Employee and Contigent Worker count.  I am pivoting off the Person Type, and this all makes sense so far, and I'm using the Assignment Count as my measure.

But it's that average……

In each of those Average Columns there is a row for the department, and then drilled into that, there is a row per management level.  The Management Levels have the total numbers of employees in those levels per that department per that vertical.  The Department Row has an average of all those sums put together.  But…they're both stored in the same column.

So like this:

My question is twofold -

* Very simply, how would I calculate that average in OTBI?  
* How would I make that one column have two different kinds of aggregates inside of it?

Thank you!

-Sean

SeanWesche · Answer

Thanks for the response gclampitt!

So basically, it seems like I can take that Assignment Count and tweak it to give the aggregation I want it to give.  How then would I control what it's grouping by?  Like, in my case here, I'd want to take the average assignment count across the job levels within each vertical.

So, this is what I'm starting with:

(I've gotten rid of the Person Subject Area.  I could only see Person Type Identifier as an option in the Workforce Assignment Subject area, which seems to just be a Database ID or something.  I'll have to figure out what those numbers stand for maybe and then make them display English words instead of numbers.)

But yeah, I've got the department, job Level And then the Assignment Count measure.  Right now the rule for that Assignment count is "Sum."  But when I go in there and change it to "average"

None of the numbers change.

There has to be a way for me to tell it "Give me the average across all Job Levels."   What average is it thinking I'm wanting when I just click it the way it is here?  What I WANT it to be doing is to add up all the job level counts within the "Administration" vertical (for example), and then give me the average number and display that instead of the total number within that job level.

gclampitt · Answer

Hello- can you not get  all that info from the one "Assignment real time" subject area?

(You should try and just use one if possible -avoids aditional joins which could maybe then result in aggregraiton issues ).

If using the Person subject area for "person count" - just create your own in the Assignment SA - "count(distinct "Worker"."Person ID")"

If you "Edit properties" of a measure you set the default aggreation rule there.

Can also set aggregation in a pivot table, eg set to Average. - is this what you mean?

You can't have multiple aggregations  - it can only be one eg sum, count, average etc.

Option might be to "duplicate layer" so you have two measures for the same thing, then can set one to sum and the other to average