topic Re: Average for a week based on selected date in QlikView

Average for a week based on selected date

makrishnaraj — Wed, 18 Nov 2015 10:07:52 GMT

Hi,

I need to calculate the Average for the 5 days say for example in the below table. If i select Report Date as 25/09/2015 then my average should display as 11.4 in the text box i.e. avg of 21/09/2015 to 25/09/2015.

ID	ReportDate	Sum(CallsAnswered)
10658	21/09/2015	8
10658	22/09/2015	9
10658	23/09/2015	14
10658	24/09/2015	14
10658	25/09/2015	12

Thanks in advance.

Regards,

Krishna

Re: Average for a week based on selected date

Anonymous — Wed, 18 Nov 2015 10:25:19 GMT

you can try sth like:

=avg({<ReportDate={'>=$(=date(max(YourDateField)-5,'DD/MM/YYYY')

<=$(=date(max(YourDateField),'DD/MM/YYYY')>} CallAnswered)

Hope this will help!!

Re: Average for a week based on selected date

makrishnaraj — Wed, 18 Nov 2015 10:33:14 GMT

Hi Balraj,

Thanks for you Information, as you have mentioned max(YourDateField), the DateField is also a ReportDate.

Re: Average for a week based on selected date

Anonymous — Wed, 18 Nov 2015 10:40:27 GMT

okay, simply try:

=avg({<ReportDate={'>=$(=date(max(ReportDate)-5,'DD/MM/YYYY')

<=$(=date(max(ReportDate),'DD/MM/YYYY')>} CallAnswered)

Re: Average for a week based on selected date

Wed, 18 Nov 2015 10:49:08 GMT

Hi,

try this:

Re: Average for a week based on selected date

makrishnaraj — Wed, 18 Nov 2015 11:09:14 GMT

Hi Balraj,

Thanks for you help.

I used the below formula & it worked for me.

=(Sum({<ReportDate={">=$(=date(max(ReportDate)-5))<=$(=date(max(ReportDate)))"}>} CallsAnswered))/5

Re: Average for a week based on selected date

HirisH_V7 — Wed, 18 Nov 2015 12:20:45 GMT

Hi,

Check out the attachment .

I hope that helps.

Regards,

Hirish