topic Re: How can I calculate an average days ? in QlikView

How can I calculate an average days ?

suzel404 — Sat, 27 Dec 2014 23:18:00 GMT

Hi all,

I want to calculate an average days of store which are starts the activity:

To calculcale the average days : ( count store per day for type 4,5,6 and count store per day-1 for type 4,5,6 ) / 2

My set analysis is : Count(DISTINCT {<CALENDAR_DATE={'$(=Date(Max(CALENDAR_DATE)))'} , TYPE= {4,5,6} > } STORE_ID)

But the result is only for the last date (May). How can I calculate the average day:

( Number store with type 4,5,6 per day + Number store with type 456 par day -1 ) / 2 in set analysis ?

Thanks for your help.

Re: How can I calculate an average days ?

Sun, 28 Dec 2014 01:55:37 GMT

In the Chart Table Add the CALENDAR_month and CALENDAR_Date as dimensions and in the expression add hte following:

RANGEAVG (Above (COUNT(STORE_ID),-1,2))

This tells QView Engine to COUNT the STORE_ID (you can use DISTINCT also) and makes an average of the range between the previous row and the current row. just make sure it is sorted by ClaendarDate also. See attached file, hope this helps.

Re: How can I calculate an average days ?

Sun, 28 Dec 2014 02:09:54 GMT

sorry, the expression is:

RANGEAVG (Above (COUNT(distinct STORE_ID),0,2))

see attached file.

Re: How can I calculate an average days ?

Sun, 28 Dec 2014 02:15:43 GMT

Dimensions:

Month and Date

expression: Average

RANGEAVG (Above (COUNT(distinct STORE_ID),0,2))

expression: count

COUNT(distinct STORE_ID)

Re: How can I calculate an average days ?

suzel404 — Sun, 28 Dec 2014 20:12:51 GMT

Thank you Mario.

It is exactly what I need !

Thank you very much.