topic Set analysis per dimension row in QlikView

Set analysis per dimension row

Anonymous — Mon, 26 Jan 2026 16:26:21 GMT

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)

Re: Set analysis per dimension row

swuehl — Tue, 24 May 2016 08:52:55 GMT

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

)

Re: Set analysis per dimension row

Anonymous — Tue, 24 May 2016 09:00:35 GMT

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

Re: Set analysis per dimension row

Anonymous — Tue, 24 May 2016 09:49:23 GMT

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?

Re: Set analysis per dimension row

swuehl — Tue, 24 May 2016 10:00:55 GMT

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)

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

)

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

Re: Set analysis per dimension row

Anonymous — Tue, 24 May 2016 11:39:08 GMT

Works perfectly, thanks!