topic Re: Calculate Averages per Different Levels in QlikView

Calculate Averages per Different Levels

Wed, 25 Nov 2020 16:16:04 GMT

I have a source file with these columns:

Person, Process, Sub-process, Item, Hours.

Process has multiple Sub-processes and Sub-process has multiple Items.

I would need to show Average hours a Person spent on each level. (with the drill down capability if possible).

I am using this expression: SUM(Hours)/Count (DISTINCT Process)

but need to be able to change it dynamically ...say if I select Sub-process or Item.

What would be a best way of doing this. (Chart or a table) and how?

Re: Calculate Averages per Different Levels

vishsaggi — Fri, 24 Feb 2017 16:30:59 GMT

Pivot table should help you in this regards? As it will automatically drill down based on your dimensions. The table will reflect based on your selections? If this is not what you are looking let us know your output.

Re: Calculate Averages per Different Levels

Fri, 24 Feb 2017 17:11:47 GMT

What is the expression I should use for pivot?

Re: Calculate Averages per Different Levels

vishsaggi — Fri, 24 Feb 2017 17:18:13 GMT

Dim:

Process

SubProcess

Item

Expr:

SUM(Hours)/Count (DISTINCT Process)

Re: Calculate Averages per Different Levels

Fri, 24 Feb 2017 18:31:37 GMT

How is this expression going to show Averages per SubProcess and per Item?

Also Person should be in Dimensions as well, right?

Re: Calculate Averages per Different Levels

vishsaggi — Fri, 24 Feb 2017 18:34:30 GMT

Can you share a sample data with expected output ?

Re: Calculate Averages per Different Levels

Fri, 24 Feb 2017 18:57:03 GMT

ok. Attached is the sample data.

Desired output should be averages on all levels (Process, Sub-process and Item) per person.

Re: Calculate Averages per Different Levels

Anonymous — Fri, 24 Feb 2017 19:25:44 GMT

Sergio,

I am having a similar problem as you are having. I will follow this thread to see if a solution is presented. In the meantime I have two charts from my own example that can display how you can show the data. The circular chart is meant to display average sales by week compared to actual sales by week. (This helps the users clearly see outlier points which can lead to deeper sales discussions). The chart on the right is a line chart which should also display actual sales vs. average sales. (The average should always be a straight line of which actual sales can be plotted against so that it can be determined which weeks should be of concern if they are not within a particular threshold compared to average weekly sales.

The formula I use for actual sales is: =sales

The formula I use for average sales is: =Sum(TOTAL Sales)/Count(Distinct Week)

My issue is I can not get my average sales to calculate correctly and be dynamic according to the number of weeks selected.

Thanks,

Brandon

Re: Calculate Averages per Different Levels

vishsaggi — Fri, 24 Feb 2017 19:48:11 GMT

I am not sure if this is what you want? Let us know? See attached.

Re: Calculate Averages per Different Levels

Fri, 24 Feb 2017 20:12:17 GMT

Thank you for your solution Vishwarath.

However, this is not what I need. E.g.

If I select Staff 2. He worked a total of 209 hrs.

That was 3 processes so average would be 209/3

That was 2 sub-processes etc. - so average of that as well. etc.

Re: Calculate Averages per Different Levels

vishsaggi — Fri, 24 Feb 2017 23:48:18 GMT

Try this? Attached.