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

[lawrenceiow]

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.

[lawrenceiow]

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.

[simenkg]

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?

[lawrenceiow]

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)

[evan_kurowski]

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.

[lawrenceiow]

OK, thanks for the advice Evan.

[lawrenceiow]

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

[simenkg]

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

I'm glad it worked out.

[lawrenceiow]

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