AVG and SUM in same chart...

[mike_spada]

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

[sunny_talwar]

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.

[mike_spada]

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

[Peter_Cammaert]

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

[Peter_Cammaert]

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

[mike_spada]

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

[mike_spada]

Ok Peter,

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

Mike

[Peter_Cammaert]

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)

[mike_spada]

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

Mike

[mike_spada]

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