Include missing data when calculating average (etc)

lawrenceiow — Wed, 30 Jul 2014 10:41:35 GMT

OK, so now that I've typed the title for my question it looks a little odd - including something that doesn't exist in a calculation.

But, as Henric Cronström said in his article The Importance of Nothing "Few things are as important to understand as the concept of nothingness". So, thanks to these articles, I know Qlikview can do it, I'm just struggling to get my head around how to correctly write the expression(s) - so far my various attempts have failed.

In brief, I need to get an average over a period of time and on some days there is no data and therefore should be counted as zero for that date.

Coming to Qlikview as an Excel user I just need to figure out how to do in Qlikview the stuff I already have learnt how to do in Excel. So, attached is an Excel mock up of a dataset that I need to create a chart for. In the spreadsheet columns A & B contain the data that I will load into my Qlikview fact table. Columns J and K show three values that I need adding to my Qlikview chart as reference lines. I've got this working (see attached qvw) fine until I select 'N' in the "Like" field - this is where I need to know how to get Qlikview to give me the results as shown in the Excel file. At the moment it does not and I assume that this is because the avg and stdev are not including the days, in the denominator, where there aren't any "N" values. Hopefully someone can explain the correct way in simple terms for me.

In very basic terms I have the follow results from a dataset (dataset has two fields: Date and Response. Response contains either "Like" or "Dislike" see attached 004.qvw):

Date	Like	Dislike
14/01/2014	3	1
15/01/2014	2	0
16/01/2014	2	0
17/01/2014	2	1

Header 1	Header 2
The StDev for Like is 0.5	=Stdev(aggr(Count(Response),Date))
The Average for Like is 2.25	=Avg(aggr(count(Response),Date))
Giving an Upper of 3.25 (Avg + 2 x StDev)	=(Avg(aggr(Count(Response),Date)))+2*(Stdev(aggr(Count(Response) ,Date)))
and a Lower of 1.25 (Avg - 2 x StDev)	=RangeMax(0,(Avg(aggr(Count(Response),Date)))-2*(Stdev(aggr(Count(Response) ,Date))))

QlikView is giving me the correct answers for Like but incorrect answers for Dislike and I presume this is because there are only two records with a Dislike - one on 14th and one on 17th. What's the correct syntax I should be using.

Thanks in advance.

Re: Include missing data when calculating average (etc)

lawrenceiow — Thu, 07 Aug 2014 14:47:18 GMT

This has been answered here: Re: Re: Why does my Avg expression not work (I mean how do I get it to work)

topic Re: Include missing data when calculating average (etc) in QlikView

Include missing data when calculating average (etc)

Re: Include missing data when calculating average (etc)