topic Re: Average per day or Something else in QlikView

Average days per application or Something else

Thu, 09 Jan 2014 15:15:45 GMT

Hi,

I have the following data. I want to calculate average decisioning days per application. I think average would not be the better to calculate it. Which one would be better method to calculate.

ID	Decisioning Days
11111	0
11112	1
11113	2
11114	1
11115	2
11116	2
11117	2
11118	1
11119	2
11120	1
11121	1
11122	3
11123	3
11124	1
11125	1
11126	2
11127	30
11128	25
11129	24

Re: Average per day or Something else

jvitantonio — Thu, 09 Jan 2014 15:24:16 GMT

Hi, you can try:

sum(Total NoOfApps)/sum(TOTAL DecisionDays)

Re: Average per day or Something else

Thu, 09 Jan 2014 15:42:42 GMT

try this:

=avg(aggr(DecisioningDays,NoofApps))

Re: Average per day or Something else

jagan — Thu, 09 Jan 2014 15:47:14 GMT

Hi,

Try like this

=Avg([Decisioning Days] * [No of Apps])

Regards,

Jagan.

Re: Average days per application or Something else

Thu, 09 Jan 2014 15:50:05 GMT

avg(aggr(DecisionDays,Application) where Application is your dimension so your are averaging the # of days by Application. It is essentially summing the # of days per Application and dividing by the # of IDs (or Row Count).

Re: Average days per application or Something else

Thu, 09 Jan 2014 15:59:49 GMT

It's not the good method because most of the applications are decisioned between 0-2 days. Very few are decisioned in greater than 2 days but these greater than 2 days applications increase the average days per applications. e.g

Decisioning Days	%
0	1
1	7
2	6
3	2
24	1
25	1
30	1
Total Applications	19

Re: Average days per application or Something else

Thu, 09 Jan 2014 16:04:14 GMT

I see. Due to your outliers, your averages will be skewed. There are ways to remove outliers but the way I like to do it is to use a scatter chart in the same tab and allow the user to use the scatter chart to highlight the range without the outliers. This way the user sees the outliers visually but can filter to only show averages without the outliers. Not a perfect solution but great during data discovery phase

Re: Average days per application or Something else

Thu, 09 Jan 2014 16:08:41 GMT

Debbie,

You are right. These outliers skew the average. I need to show in a table. Any idea?

Re: Average days per application or Something else

Thu, 09 Jan 2014 16:24:36 GMT

this will give you the right avg:

=sum(DecisioningDays*NoofApps)/sum(NoofApps)

Re: Average days per application or Something else

Thu, 09 Jan 2014 17:12:39 GMT

It gives the same answer. Still not solved

Re: Average days per application or Something else

iktrayanov — Thu, 09 Jan 2014 17:33:06 GMT

Why don't you try

median(aggr(DecisionDays,Application)

Re: Average days per application or Something else

iktrayanov — Thu, 09 Jan 2014 17:37:02 GMT

Another option to consider

from QlikView Help:

mode([{set_expression}][ distinct ] expression)

Returns the mode value, i.e. the most commonly occurring value, of expression or field iterated over the chart dimension(s). If more than one value is equally commonly occurring, NULL is returned. Mode can return numeric values as well as text values.

Mode does not support the total qualifier.

Examples:

mode( Product )

mode( X*Y/3 )

Re: Average days per application or Something else

Fri, 10 Jan 2014 08:39:22 GMT

how should I write expression for my data?

Re: Average days per application or Something else

Fri, 10 Jan 2014 09:04:03 GMT

If you are familiar with standard deviation you can use that to exclude

this values that are outside of the acceptable range but I would put a

toggle to allow the user to turn the exclusion off and on and allow them to

drill into the data to see the outliers. Never good to exclude data. You

want to investigate the cause of these outliers whether bad data or not.

Re: Average days per application or Something else

Fri, 10 Jan 2014 10:05:21 GMT

Then how should I do with this data?

Re: Average days per application or Something else

Tue, 14 Jan 2014 15:16:28 GMT

sorry for lack of responding. I have been tied up with a client. Let me look back to an application I created years ago using the standard deviation calculation as a filter to highlight and include/exclude outliers. From what I recall it worked for what the business wanted.

Re: Average days per application or Something else

Mon, 27 Jan 2014 11:33:48 GMT

Hi Debbie,

Apologies, I was out of office. Did you get any chance to have a look on it.