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;
