Set analysis per dimension row

[Anonymous]

I have an expression that is used to calculate the number of jobs done by engineers per day.

It does this by counting the jobs done and dividing by the number of days in which there was a job.

Trouble is, some engineers work some days while others don't. For example if 2 engineers worked on Monday and only one did on Tuesday and both engineers did 5 jobs each day.

Currently the expression would be 15 jobs / 2 days worked = 7.5 jobs per day.

Really I want to do Engineer 1 = 10 jobs / 2 days = 5 per day, Engineer 2 = 5 jobs / 1 day = 5 jobs per day. 5 + 5 = 10 jobs per day.

How can I adapt the expression below to achieve what I'm after?

COUNT({$<TesseractKey = {">0"}, TesseractCallTypes.EstimatedWork-={'0'}, TesseractCallCompletedTime.Year={$(=$(vSelectedCompletedYearMax))}>} TesseractCountKey)
/
COUNT(DISTINCT {$<TesseractKey = {">0"}, TesseractCallTypes.GroupDescription = , TesseractCallTypes.EstimatedWork = , TesseractCallTypes.Code = , TesseractCallTypes.EstimatedWork-={'0'}, TesseractCallCompletedTime.Year={$(=$(vSelectedCompletedYearMax))}>} TesseractCallCompletedTime.Date)

[swuehl]

How do you get to 10 jobs per day in total?

Or do you mean 5 jobs per day (average for engineer)?

Then try advanced aggregation:

Avg(

Aggr(

     COUNT({$<TesseractKey = {">0"}, TesseractCallTypes.EstimatedWork -=    {'0'}, TesseractCallCompletedTime.Year=     {$(=$(vSelectedCompletedYearMax))}>} TesseractCountKey) 

     / 

COUNT(DISTINCT {$<TesseractKey = {">0"}, TesseractCallTypes.GroupDescription = , TesseractCallTypes.EstimatedWork = , TesseractCallTypes.Code = , TesseractCallTypes.EstimatedWork-={'0'}, TesseractCallCompletedTime.Year={$(=$(vSelectedCompletedYearMax))}>} TesseractCallCompletedTime.Date)

, YourEngineerIDField

)

)

[Anonymous]

To get 10 I did, each engineer did 5 jobs per day, SUM them up to get 10 jobs per day across all engineers.

That Aggr function works perfectly, I just changed the Avg to Sum to get what I'm after, thanks!.

[Anonymous]

Sorry, looks like I spoke too soon.

In the table I have month dimension (among others, in a cyclic group). When I select an individual month the calculation is correct however when I don't select a month so they are all listed the figure shown in each month row is different to when selecting each individual month.

I've read up on the Aggr function, but can't figure out what's going on here, does anyone have any ideas?

[swuehl · Accepted solution]

The advanced aggregation won't group the number of jobs/ per day  per time base, only per engineer, then average the numbers.

If you want to consider a chart chart dimension,you can try adding it to the advanced aggregation dimension list.

Since you are using a dimension group, try

Avg(

Aggr(

     COUNT({$<TesseractKey = {">0"}, TesseractCallTypes.EstimatedWork -=    {'0'}, TesseractCallCompletedTime.Year=     {$(=$(vSelectedCompletedYearMax))}>} TesseractCountKey)

     /

COUNT(DISTINCT {$<TesseractKey = {">0"}, TesseractCallTypes.GroupDescription = , TesseractCallTypes.EstimatedWork = , TesseractCallTypes.Code = , TesseractCallTypes.EstimatedWork-={'0'}, TesseractCallCompletedTime.Year={$(=$(vSelectedCompletedYearMax))}>} TesseractCallCompletedTime.Date)

, YourEngineerIDField, [$(=GetCurrentField( YourDimensionGroupName ))]

)

)

If this does not help, then please post a small sample QVW that demonstrates your model and requirement.

[Anonymous]

Works perfectly, thanks!