Why does my Avg expression not work (I mean how do I get it to work)

[lawrenceiow]

So, I can do this in Excel but can't think how I do it in QlikView, please can someone help.

Date	Response	Quantity
14/01/2014	Like	1
14/01/2014	Like	1
14/01/2014	Dislike	1
14/01/2014	Like	1
15/01/2014	Like	1
15/01/2014	Like	1
16/01/2014	Like	1
16/01/2014	Like	1
17/01/2014	Like	1
17/01/2014	Like	1
17/01/2014	Dislike	1

So, above I have a simplified version of my data which can be summarised as:

14/01/2014  Like = 3, Dislike = 1

15/01/2014  Like = 2, Dislike = 0

16/01/2014  Like = 2, Dislike = 0

17/01/2014  Like = 2, Dislike = 1

Over the period, I have an average of 2.25 (9/4) likes and 0.5 (2/4) dislikes. What expression should I be using in QlikView because the one I'm using gives me 2.25 When Like is selected in the list box but gives 1 when dislike is selected.

Expression : Avg(Aggr(sum(Quantity),Date))

I also need a standard deviation and an upper and lower limit.  As I say, the above is a simplified version of my data which has around 80,000 records over two years and a variety of filters than need applying (not just Like or Dislike).  I've created the chart and I just need to add reference lines for avg, upper and lower. These were all setup and worked well right up until I started applying filters which resulted in there being no data on some days.

Excel has been providing the correct answers for years and I now need to replace the spreadsheet with a QlikView version.

I originally posted this question here Include missing data when calculating average (etc) but I probably worded it really badly as no one has been able to help. So, I'm hoping by re-wording it here someone will understand what I'm trying to acheive.

Lawrence

[#2]

Hi,

If you want Stanadard Desviation see this disscusion:

Calculate standard desviation in a pivot table

or

look for Gauss Chart if you need it.

Best regards.

[JonnyPoole]

I used your data to create an example (attached).

Used SET ANALYSIS to come up with % liked / disliked by day

%  Liked:  (disregards user selection on like/dislike field)

sum( {$<[Like/Disklike]={'Like'}>}  Count)    /   sum( {$<[Like/Disklike]=>} total <Date> Count)

%   DisLiked:  (disregards user selection on like/dislike field)

sum( {$<[Like/Disklike]={'Dislike'}>}  Count)    /   sum( {$<[Like/Disklike]=>} total <Date> Count)

[lawrenceiow]

Thank you for your reply, Oswaldo. I already have an expression for standard deviation, in fact I all the expressions I need and they give me the correct answers BUT, they only give me the correct answers when I have data for all dates. I've shown the expression that I use for the average in the hope that someone will point out what I've done wrong, I can then apply the same principal to my other expressions.

If it helps, the expressions I've used are:

=Stdev(aggr(Sum(Qty),Date))  for the standard deviation

=(Avg(aggr(sum(Qty),Date)))+2*(Stdev(aggr(sum(Qty),Date)))   for the Upper limit

=(RangeMax(0,(Avg(aggr(sum(Qty),Date)))-2*(Stdev(aggr(sum(Qty),Date)))))   for the Lower limit (but no lower than 0)

[lawrenceiow]

Thank you for your reply Jonathon and for taking the time to create the qvw file. However, I do need the user to be able to select Like or Dislike so that they can "explore" the data and make "business discoveries".

I wonder if you would mind taking a look at my qvw file. I have also attached a simplified version of the Excel file (just in case you are able to see how I do it in Excel).

As you will see, I have a chart which I am adding three reference lines to give Average, Upper and Lower (StDev is required for the upper and lower).

To start with (ie with nothing selected) the correct values are shown. Selecting Like again gives the correct answers. However, selecting Dislike does not give the correct answer.

I hope you are able to take a look at the qvw for me as I'm sure the answer is simple, if only I could explain myself clearly.

Thank you again,

Lawrence

[simenkg]

Your problem is that avg() only averages over non-missing values.

The response Dislike only exists for 2 dates and therefore you get 2/2 = 1.

Try this expression in stead:

sum(Quantity)/count({1}distinct Date)

[lawrenceiow]

Brilliant! Thanks Simen. I knew what was causing the problem ie "The response Dislike only exists for 2 dates and therefore you get 2/2 = 1" but I didn't know how to fix it. So, now we've got the Avg working by not using Avg, how do we get the StDev working without using StDev???

[JonnyPoole]

in your example is the correct average for dislike = 1  (because on the two dates that has dislikes there was 1 dislike count on each date.... 2/2 = 1) 

or is the correct answer 0.5 (because there were 2 dislike counts over 2 days but there were 2 other days with no dislikes  ... 2/4 = .5

[simenkg · Accepted solution]

how about something like:

sqrt(sum(aggr(pow(sum(Quantity)-sum(total Quantity)/count(total distinct Date),2),Date))/Count(TOTAL DISTINCT Date))

[evan_kurowski]

Hello lawrenceiow,

It looks like your metric is defined as:

(Sum of Quantity In Selection / Number of TOTAL distinct Dates)

The Avg(Aggr()) combination can be tricky, especially with regards to text box usage where sometimes selections in the Aggr field, or the Aggr field being involved in a table join will introduce a Null() slot to the denominator, thereby spreading out the average over values + 1 (similar to the way charts generate a Null dimension).

You may have an easier time avoiding Avg(), and using the 2-part calculation (Sum / Count)
Sum(Quantity) / Count(DISTINCT {<Response>} Date)