topic Re: AVG and SUM in same chart... in QlikView

AVG and SUM in same chart...

mike_spada — Mon, 28 Dec 2015 14:41:10 GMT

Hi experts,

I have an issue with this scenario: I have these data:

Division	2014	AVG 2014
2 IT	849,00	38,59
3 GRAPHICS	567,75	113,55
4 ADMINISTRATION	1.221,00	203,50
5 PRODUCTION	6.385,50	187,81
6 SELLINGS	686,00	228,67
7 PURCHASES	331,00	165,50
8 QUALITY	1.707,75	131,37
9 WAREHOUSE	757,75	252,58
11 TOOLS	425,75	212,88
Totals	12.931,50	1.534,44

I want to obtain a chart like the one in the picture attached, in which as dimension I have the Divisions, in left the sum of column 2014 (bar chart) and a line chart which average (column AVG 2014).

I'm trying but don't succeed in build it... anyone can help please?

Thanks,

Mike

Re: AVG and SUM in same chart...

sunny_talwar — Mon, 28 Dec 2015 15:16:34 GMT

The provided data is already aggregated, can you provide non-aggregated sample so that we can help you. It would be even better if you can provide the sample where it isn't working for you.

Re: AVG and SUM in same chart...

mike_spada — Mon, 28 Dec 2015 15:21:04 GMT

Hi,

it's not simple because is a big application with many complications.

I'll try to post soon.

To calculate 2014 amount I use a set analysis like

sum({ $< itemCode = {'0353'}>} amount)

and it works: I want to sum amount columne for itemCode = 0353

But I don't know how use avg...

Mike

Re: AVG and SUM in same chart...

Peter_Cammaert — Mon, 28 Dec 2015 15:38:08 GMT

Create a combo chart
Ad dimension, select Divisions
As expressions, add the sum() and avg() expressions you used to produce the data in your OP.
For each expression, select the correct "Display Options": Bar & Line. For the line chart, add a symbol of your choice (triangles)
In the Axis tab, select the Sum expression and in Position select Left. For the other expression select Position = Right.
In the Axis tab, enable both axes by setting their width to at least 1 pixel. Enable "Show Grid" for both dimension and expressions.

That should about do it.

Peter

PS. There is no Totals-amount in your image so there won't be any in my explanation.

Re: AVG and SUM in same chart...

Peter_Cammaert — Mon, 28 Dec 2015 15:47:48 GMT

What kind of average are you talking about? Monthly average? Average per product item? Average per employee? ....

Re: AVG and SUM in same chart...

mike_spada — Mon, 28 Dec 2015 15:56:17 GMT

Hi Peter,

we are talking about hours for cost center. So, my sum is: "total of hours for cost center", the average is "average of hours for employee in that cost center".

Mike

Re: AVG and SUM in same chart...

mike_spada — Mon, 28 Dec 2015 15:56:59 GMT

Ok Peter,

under graphic aspect it works, thanks... but I have problem with average calculation yet...

Mike

Re: AVG and SUM in same chart...

Peter_Cammaert — Mon, 28 Dec 2015 16:24:36 GMT

Are your cost centers exclusive to Divisions, or do they overlap multiple divs?

got it. The sum already handles Cost Centers, so the avg will too. Only one quesiton. What exactly is ItemCode and how do you want it to impact the average?

As a first attempt, you can try (replace hours with amount if you use field amount to store a number of hours...):

=sum({ $<itemCode = {'0353'}>} hours) / count({ $<itemCode = {'0353'}>} distinct employeeID)

Re: AVG and SUM in same chart...

mike_spada — Mon, 28 Dec 2015 16:26:06 GMT

Sorry, in my representation Cost Center = Division...

Mike

Re: AVG and SUM in same chart...

mike_spada — Mon, 28 Dec 2015 17:40:43 GMT

ItemCode 0353 is an item that contain a particular type of hours (for examples, over times) that are the object of this analysis. In my app every voice contains a type of hour (is a hr app).

Mike

Re: AVG and SUM in same chart...

mike_spada — Mon, 28 Dec 2015 17:48:38 GMT

I succeed in make my chart. Thanks to Peter that tell me how to have a chart as required.

For the formula, I use something like this:

Total:

= sum({ 1< itemCode = {'0353'}, year={$(=Max(year))},month = ,company={$(=Max(company))}>} amount)

Number of Employees (not shown in chart but calculated with a formula in others part of my app):

sum({ $<

itemCode2ndType = {'3603'},year={$(=Max(year))},month={$(=Max(month))}>} totalEmployees)

Average:

sum({ 1< itemCode = {'0353'},year={$(=Max(year))},month = ,company={$(=Max(company))}>} amount)

sum({ $< itemCode2ndType = {'3603'},year={$(=Max(year))},month={$(=Max(month))}>} totalEmployees)

I don't know if there is a better way to obtain this result... if anyone can see what I don't see 🙂 I am open minded 🙂

Thanks a lot

Mike