topic Re: Calculating average in QlikView

Calculating average

Mon, 19 Oct 2015 15:41:04 GMT

hey everyone.

So i am creating a report that needs to calculate the average per department.

My data is as followed:

Name	Department	Calls
Mike	A	17
Jason	A	12
Jan	A	67
Wesley	B	34
Diana	B	28
Laura	B	95
Betty	C	19
Michael	C	12
Anna	C	27

Average		34.56
Department	Calls
A	96
B	157
C	58

Average	103.67

Now i dont want to use straight tables or pivot tables, for this specific report i am using text objects for better visualization.

The formula in the text object is:

=Num(Sum({<Week={$(vCurrentWeek)}>}distinct tAgentCalls),'#,##0.00')

tAgentCalls being the field in which i have the data of the amount of calls obviously.

Now if i would do an average on this formula it would calculate the average of all employees in that selection.

Which would result in the average as seen above 34.56.

While i need it to calculate the average of the departments they are in.

Now on this specific data i have 3 people on each department, but as you can imagine this varies.

Can anybody help me with this.

Re: Calculating average

sunny_talwar — Mon, 19 Oct 2015 17:11:44 GMT

Try this:

Avg(Aggr(Sum({<Week={$(vCurrentWeek)}>}distinct tAgentCalls), Department))

Avg({<Week={$(vCurrentWeek)}>} Aggr(Sum({<Week={$(vCurrentWeek)}>} Distinct tAgentCalls), Department))

Re: Calculating average

sunny_talwar — Mon, 19 Oct 2015 17:15:06 GMT

Also find attached the same with the expected output:

Re: Calculating average

maxgro — Mon, 19 Oct 2015 17:18:34 GMT

I think

Num(Avg(Aggr(Sum(tAgentCalls), Department)),'#,##0.00')

without distinct

add the set modifier if required

<Week={$(vCurrentWeek)}>

Re: Calculating average

Oleg_Troyansky — Mon, 19 Oct 2015 17:18:47 GMT

Hi Mike,

while it's technically possible to calculate your desired result in a text object, the solution would be rather convoluted and very difficult to maintain.

Let me challenge your initial premise - you opted for a text object "for better visualization". Since your end result includes multiple departments and multiple averages, the Straight Table is the book answer for that. If you don't like captions, borders, backgrounds, etc. - all of that can be disabled or tweaked to reach the kind of a visualization that you desire, while keeping tour logic simple.

For example, turn on the Design grid and right-click on the Straight Table cell. Check "Custom Design Cell". You will get full control over colors, borders, etc... Anything you don't like can be turned off using transparent color.

If you'd like to post a picture of your desired visualization, I could possibly show you how to do it in a Straight Table.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Calculating average

Tue, 20 Oct 2015 06:23:11 GMT

Hey Oleg,

Thank you for your answer, here is the screenshot.

If you could tell me how to create this with a straight table it would be very much appreciated!

Kind regards,

Mike Cleven.

Re: Calculating average

Tue, 20 Oct 2015 08:03:48 GMT

Hey Sunny, thank you,

I had to adjust it a tad to suit my real data set but now it does what i want perfectly.

Re: Calculating average

Tue, 20 Oct 2015 08:07:51 GMT

I do have 1 question, can i somehow always show the average of ALL departments instead of just the ones i select? in my dataset i have over 50 departments.

I would like it to show the average of all departments at all times.

Re: Calculating average

sunny_talwar — Tue, 20 Oct 2015 15:15:06 GMT

You just need to add {<Department = >} in your set analysis field to make sure any selection in Department doesn't change anything in your output. If you want this to now change based on any selection you can use {1}

So for the give example, use this:

=Num(Avg({1}Aggr(Sum({1}Calls), Department)), '#,##0.00') //for no impact on any selection or

=Num(Avg({<Department = >}Aggr(Sum({<Department = >}Calls), Department)), '#,##0.00') //for no impact on just Department Selection.

Re: Calculating average

Oleg_Troyansky — Tue, 20 Oct 2015 15:32:28 GMT

Hi Mike,

clearly, this dashboard doesn't show averages per department, as you initially asked for. Instead, you are showing the total average, even if you pre-aggregated it by department.

In this case, what you need is nested aggregation, and the formula suggested by Sunny is the right way to go (depending on what you need to do about user selections).

cheers,

Oleg Troyansky

Re: Calculating average

Wed, 21 Oct 2015 06:20:12 GMT

Hey Oleg,

Sorry for the confusion, i indeed meant i only needed a calculation on a department level instead of on all departments.

My apologies.

Thank you for your time.