What I am trying to do is show a date by hour. The end result would look like the following. I also tried the example in the cookbook, but it does not work because I have way too much data. I have over 1 million rows that I need to do this for.
| INTERVAL | TOTAL |
| 0045 - 0145 | 38 |
| 0145 - 0245 | 35 |
| 0245 - 0345 | 26 |
| 0345 - 0445 | 19 |
| 0445 - 0545 | 0 |
| 0545 - 0645 | 0 |
| 0645 - 0745 | 0 |
| 0745 - 0845 | 18 |
| 0845 - 0945 | 27 |
| 0945 - 1045 | 29 |
| 1045 - 1145 | 35 |
| 1145 - 1245 | 32 |
| 1245 - 1345 | 40 |
| 1345 - 1445 | 38 |
| 1445 - 1545 | 37 |
| 1545 - 1645 | 43 |
| 1645 - 1745 | 53 |
| 1745 - 1845 | 61 |
| 1845 - 1945 | 63 |
| 1945 - 2045 | 57 |
| 2045 - 2145 | 61 |
| 2145 - 2245 | 54 |
| 2245 - 2345 | 43 |
The issue I am running into is that when I have 0 it is not showing the interval. I also can't uncheck supress 0 values because then it shows everything and not just the dates I have chosen. Here is what I have so far. This gets me everything I want except for the 0 values are not showing.
TEMP_MIN_MAX_DATES:LOAD
MIN(CONVERTED_ADT_ARRIVAL_TIME) as MIN_ADT_ARRIVAL_TIME,
MAX(CONVERTED_ADT_ARRIVAL_TIME) as MAX_ADT_ARRIVAL_TIMERESIDENT ED_EVENT_INFO;
LET vMinDate=peek('MIN_ADT_ARRIVAL_TIME',-1,'TEMP_MIN_MAX_DATES');LET vMaxDate=peek('MAX_ADT_ARRIVAL_TIME',-1,'TEMP_MIN_MAX_DATES');DROP TABLE TEMP_MIN_MAX_DATES;
TempCalendar:LOAD//TimeStamp($(vMinDate) -(1395/1440) + ((RecNo()-1)/24) + (IterNo() -1)) AS STARTTimeStamp($(vMinDate) + 3/96 + ((RecNo()-1)/24) + (IterNo() -1)) AS StartDTTM
,TimeStamp(($(vMinDate) + 3/96 + ((RecNo()-1)/24) + (IterNo() -1)) + (1/24)) AS EndDTTMAutoGenerate 24 WHILE NUM($(vMinDate) + IterNo() -1) <= Num($(vMaxDate)) + 3/96 ;
IslandCalendar:load
StartDTTM
,EndDTTM
,MONTH(FLOOR(StartDTTM)) AS StartMonth
,YEAR(FLOOR(StartDTTM)) AS StartYear
,DATE(FLOOR(StartDTTM)) AS StartDt
,TIME(FRAC(StartDTTM)) AS StartTime
,TIME(FRAC(EndDTTM)) AS EndTimeRESIDENT TempCalendar
;
DROP TABLE TempCalendar;
This is the expression I am using in the chart.
=
SUM(if(DTTM_PAT_ARR <= EndDTTM AND DTTM_PAT_ROOMED >= EndDTTM,COUNTER_CSN
))
Thanks for any help.
