topic Re: Why does my Avg expression not work (I mean how do I get it to work) in QlikView

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

lawrenceiow — Wed, 06 Aug 2014 16:12:56 GMT

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

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

Wed, 06 Aug 2014 16:38:56 GMT

Hi,

If you want Stanadard Desviation see this disscusion:

Calculate standard desviation in a pivot table

look for Gauss Chart if you need it.

Best regards.

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

JonnyPoole — Wed, 06 Aug 2014 16:55:30 GMT

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)

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

lawrenceiow — Wed, 06 Aug 2014 16:58:15 GMT

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)

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

lawrenceiow — Wed, 06 Aug 2014 17:26:29 GMT

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

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

simenkg — Wed, 06 Aug 2014 17:34:52 GMT

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)

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

lawrenceiow — Wed, 06 Aug 2014 17:43:33 GMT

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???

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

JonnyPoole — Wed, 06 Aug 2014 17:50:34 GMT

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

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

simenkg — Wed, 06 Aug 2014 17:57:54 GMT

how about something like:

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

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

evan_kurowski — Wed, 06 Aug 2014 21:11:03 GMT

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)

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

lawrenceiow — Thu, 07 Aug 2014 12:42:37 GMT

Jonathan,

My denominator will always need to be the number of days in the selected period of time (Year, month, Qtr, Mon-Frid, whatever) irrespective of what other selections are made. The numerator would relate to whatever was selected.

I had hoped that if I used aggr(sum(Quantity), Date), which I understood to mean "give me the total of Quantity for each Date", then I could also use aggr(sum(Quantity), {<Response=>}Date) to mean "give me the total of Quantity for all dates, ignoring the Response selection". But it would seem that's not valid syntax.

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

lawrenceiow — Thu, 07 Aug 2014 12:51:34 GMT

Simen,

Did you fall asleep on the keyboard? Are you sure all those functions are allowed to exist in one expression?

Seriously though, your expression has given me a number. It is a number which is different to the number Excel gives me (notice I have avoided using the word "wrong").. I have a statistican visiting me this afternoon so I will ask him which he thinks is the correct answer - your answer or the Excel answer. Hopefully he will agree with your answer (for all I know you may be a statistician yourself) and I can move on with my dashboard design.

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

simenkg — Thu, 07 Aug 2014 13:02:27 GMT

Hehe, I used this page to test the numbers: Standard Deviation Calculator - Calculate mean, variance of the numbers

with the test numbers 3,2,2,2 for like i get a

Population Standard Deviation of 0.43301 which I also get in QlikView.

What number do you expect?

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

lawrenceiow — Thu, 07 Aug 2014 13:05:43 GMT

Thanks Evan,

You are right, using your 'long-hand' method has correctly given me the average, thank you. All I need now is the 'long-hand' method for StdDev.

I must say, I was rather hoping that I could use the {<Response>} bit of set analysis against the Date part of Aggr() but it would seem that is not allowed.

e.g. Avg(Aggr(Sum(Quantity), {<Response>} Date)

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

evan_kurowski — Thu, 07 Aug 2014 13:34:51 GMT

Also, one thing you may want to consider is that the "long-hand" method can sometimes be more consistent for total calculations in objects like Pivot tables.

At one point or another, collapsing/expanding dimensions, pivoting, and partial sum have all broken my Avg() expressions or totals. Trying to keep the Aggr() dimension arguments in lockstep to the chart dimensions can add complexity (especially if the chart is using dynamic dimensions). Using the sum/count method in my experience has been more sturdy and perhaps prevents some "Avg of the avg" rollup distortions.

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

lawrenceiow — Thu, 07 Aug 2014 13:51:23 GMT

OK, thanks for the advice Evan.

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

lawrenceiow — Thu, 07 Aug 2014 14:37:16 GMT

Brilliant yet again Simen! Thank you very much for your help.

Whilst your expression gave a different answer to Excel it is because your expression is calculating StDevP and my Excel formula is StDev. When I change Excel to StDevP it matches yours.

However, when I make a selection in my "Response" list box it gives the wrong answer. This is because (using my example) I do not have a "Dislike" response for every date. Following the advice from others I have put a Set Analysis modifier into your expression. This then gives me the correct answer.

So, the final answer is....

sqrt(sum(aggr(pow(sum(Qty)-sum(total Qty)/count(total distinct {<Response>} Date),2),Date))/Count(TOTAL DISTINCT Date))

I just hope no one else in my office sees the expression in my chart and asks me to explain it! For myself, I'll add a comment wherever I use this to explain it is the "long hand" version of StDevP because, I just know, when I look at this six months down the line I won't remember where it came from or what it does.

Thanks again Simen Kind Gulbrandsen

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

simenkg — Thu, 07 Aug 2014 19:29:13 GMT

The above reply was stuck in moderation queue for some hours.

I'm glad it worked out.

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

lawrenceiow — Fri, 08 Aug 2014 07:38:47 GMT

Yeah, thanks Simen. I received an email with the post that got "stuck" and, of course, when I clicked the link in the email to "go to the message on Qlik Community" I ended up on a page that stated I was not authorised to view the comment.

I presume the hold up was due to you using a link to a non-QlikView website. That website looks like it might be useful so thanks for that and thank you for all your help.

Regards

Lawrence