STDEV() and Set Analysis

[#1]

Hello Community,

i have a Table that containes values for ACD-Data like this:

SERVICE   AGENT   KPI         VALUE

100               1          calls          20

100               2          calls          15

100               3          calls          30

200               1          time          60

200               2          time          42

200               3          time          81

Then there is a formula with set analysis, that creates the average time per call

sum({$<KPI = {'time'}>} VALUE) / sum({$<KPI = {'calls'}>} VALUE)

Now i need to build the STDEV for the average time per call. Obviusly simply putting the above formula into the stdev as an argument doens't do the trick. Any Ideas how to solve this without having to remodel the tabel or precalculate the KPI?

Thanks for suggestions and hints if not solutions

Best,

Hannes

[swuehl]

Hi Hannes,

I was suggesting a table chart (agreed missing the term chart here), not a separate table in the data model itself.

Just to clarify the granularity needed for your requirements. The advanced aggregation aggr() is also creating an internal table, but it may be hard to understand the details. That's why I suggested to create a (straight) table chart first, to check if the detailed values on the required granularity level can be calculated and to derive further KPI like average or standard deviation from that. Just as an intermediate step.

Sorry if this sounds kind of confusing.

[#3]

Hi Henric and swuehl,

The formula Henric has provided is the right on

Stdev (aggr (sum ({$<KPI={'time'}>} VALUE) / sum ({$<KPI={'calls'}>} VALUE),Service,Agent)) does the trick.

I just don't understand why I have to group by Service and Agent when the dimension in a chart is service.

Any insights on this? Be it blogs, whitepapers, a post here ...

I really want to embrace the concept, since this might open up new possibilties in app dev i have never in 5 years looked into. And i need to correctly adopt this example to the real data modell...

Much appreciation and best regards

Hannes

[swuehl]

You may want to have a look at

QlikView Technical Brief - AGGR.docx

What I think comes short in the technical brief doc as well as in the HELP or most explanations is that the advanced aggregation internal table is filtered or projected on the chart dimension value when evaluated.

Hm, maybe this is still a little too abstract. I assume you are aware of basic statistics, so we don't need to dive into standard deviation calculation and that there is not much sense in calculating standard deviation on 1 sample?

Ok, then we could concentrate on the aggr() functions in your chart. But this time, I would like to suggest that you create the internal table that these functions create, and outline these tables in your answer.

I believe you are going to see the difference and the need for Service as well as Agent dimension as soon as you try to follow QlikView's calculation step by step.

[#5]

Hi swuehl,

thanks a lot. having given this a first look, i tend to believe that's exactly what i need.

I'll work my self through it and keep you posted, showing all the tables i'll create throughout the process.

Really appreciate the support i am recieving here!

Best

Hannes

[#6]

Hi swuehl,

i think i got the message. Thx again for the document. Helps a lot.

The aggr() grouped by service and agent provides the data for each combination of service and agent, thus enabling the stdev()  [or max(), or ...]  to plot over all agents within a service.

Grouping only by service in the aggr() would return only one sample. So the stdev() cannot give the result i need.

Here are the tables i created:

first the aggr() over service and agent:

Second the nested aggr with stdev(aggr()) over service and agent. chart dimension = service:

So in nested aggregations - as a rule of thumb -  i need to provide data on a granularity level that enables the last (i.e. stdev()) aggregation enough samples to work with (i.e. data on an agent level)

I managed to succesfully implement this into the real data model. Without the document and your help i would have struggled or more likely failed to do so. So thanks a million.

Best Regards

Hannes

[swuehl]

You are welcome.

There are a lot more documents here in the forum that will be of interest.

I highly recommend to read all of Henric's blog posts, which link to the technical brief documents for more details.