Average of events in a year

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

My application has a bar chart which shows all events that occurs per day. For example, if two events occurs in a same day the chart shows it stacked. For this chart the dimension used is weekdates from a master calendar and the expression counts the number of events. In this case everything is ok.

What I would like to do now is to show the average number of events per day in a yearly/monthly view. Assuming that not all days have an event, so I cannot get the total number and divide by 365.

The desired output is a chart that shows the average events per day in 2018 of 1.75, but if I filter the month January it shows the average of 2.

Do you guys have an idea of how I can generate this visibility?

The two tables are below:

'Table Events':

Date_Event	Event	Type of Event
01/01/2018	1	A
01/01/2018	1	B
01/02/2018	1	B
01/02/2018	1	A
01/05/2018	1	C
01/05/2018	1	B
02/05/2018	1	C
01/02/2017	1	A
01/03/2017	1	B
01/03/2017	1	C
01/02/2017	1	A
01/01/2017	1	B
01/01/2017	1	B
05/10/2017	1	A
05/10/2017	1	A
01/04/2017	1	C

Temp:

LOAD

min(Date_Event) as minDate,

max(Date_Event) as maxDate

Resident 'Table Events';

Let varMinDate = num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

Load

$(varMinDate) + IterNo()-1 as Num,

Date($(varMinDate) + IterNo()-1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo()-1 <= $(varMaxDate);

MasterCalendar:

load

TempDate as DATE_MTX_ONE,

Year(TempDate) as Year,

Month(TempDate) as Month,

date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear

Resident TempCalendar

Order By TempDate ASC;

DROP Table TempCalendar;

Re: Average of events in a year

aarkay29 — Wed, 08 Aug 2018 15:22:53 GMT

Try

sum(Event)/count(distinct Date_Event)

Re: Average of events in a year

Anonymous — Wed, 08 Aug 2018 19:31:13 GMT

It worked perfectly.

Thanks aarkay29‌!

topic Re: Average of events in a year in QlikView

Average of events in a year

Re: Average of events in a year

Re: Average of events in a year