Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
anselmo_meira
Contributor II

Average of events in a year

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_EventEventType of Event
01/01/2018

1

A
01/01/20181B
01/02/20181B
01/02/20181A
01/05/20181C
01/05/20181B
02/05/20181C
01/02/20171A
01/03/20171B
01/03/20171C
01/02/20171A
01/01/20171B
01/01/20171B
05/10/20171A
05/10/20171A
01/04/20171C

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;

1 Solution

Accepted Solutions
aarkay29
Valued Contributor

Re: Average of events in a year

Try

sum(Event)/count(distinct Date_Event)

2 Replies
aarkay29
Valued Contributor

Re: Average of events in a year

Try

sum(Event)/count(distinct Date_Event)

anselmo_meira
Contributor II

Re: Average of events in a year

It worked perfectly.

Thanks aarkay29‌!