Count NULL Values (time)

[drew61199]

I have a table of facilities with scheduled and actual opening / closing times by date (sample is 3 days). For background, in load script I have dimension as TIMESTAMP(OPEN_TIME,'hh:mm') as OPEN_TIME (data comes as date / time). I've also tried naming Set NullAsValue = 'NA',  but that doesn't seem to get rid of the hyphens in my display tables. 

I need to show count for those that open late / close early and also those that don't open at all. I figured out the first part, but cannot figure out the count for non-opening. When I create my pivot table (list of facilities as rows, dates as columns), the values show as hyphens (presumably NULL) on the days a facility didn't open. I've tried so many formulas I can't remember, but a few are:

NULLCOUNT(OPEN_TIME)

COUNT(IF(ISNULL(OPEN_TIME), FACILITY)

SUM(IF(ISNULL(OPEN_TIME) OR LEN(TRIM(OPEN_TIME))=0 OR OPEN_TIME=0,1,0))

COUNT(IF(OPEN_TIME= '', FACILITY))

Results returned are:

0

57,572

1

1

I have the same data in Excel and know on Day 1, it's 154, Day 2 is 182, and Day 3 is 371, so my results are nowhere close. 

[Digvijay_Singh]

Can you share a sample, you mentioned about 3 days sample but don't see any attachment.

You may try Count({<OPEN_TIME -= {"*"}>}FACILITY)

Pivot tables sometime shows null in the cells even though we don't have respective null values in the actual data model tables. It normally happens when fields in the pivot table comes from different tables. Sometimes association between two tables result into null values in the pivot table cells.

Thanks,

[MayilVahanan]

Hi 

Both are in same table? If not, can you show ur data model? And Sample data?

[drew61199]

Thanks for the reply. Sadly that returns 0. I attached a small sample and highlighted the facility that I snipped from below to show the data is non-existent for 2/2. I will preface that I'm fairly new to QLIK and this model is being built as a prototype based on two Excel files as we're currently getting the data connections established. 

Snip of an example within pivot table...

....and same facility within a regular table. 

[drew61199]

I hate to add onto this, but I realized today that my count for late opening was not accurate too. I'm using 

=sum(aggr(if(interval(Timestamp#(MAX(OPEN_TIME), 'hh:mm') - Timestamp#(MAX(OPEN), 'hh:mm'), 'mm') > 0,1,0),FACILITY))

This works fine when filtered to a single day, but obviously multiple days takes max across all days for OPEN_TIME and compares to max for all days of OPEN. I also created a new Master Measure:

INTERVAL(TIMESTAMP#(OPEN_TIME, 'hh:mm') - TIMESTAMP#(OPEN, 'hh:mm'), 'mm')   

and have it formatted for duration "mm" so it shows as minutes (negative for facilities that open early and positive if they open late). This works perfectly in a pivot or straight table, but I cannot figure out the syntax to count the ones that open late (positive value). Below I have snips of pivot table and then data exported to Excel. Clearly the Variance is a negative / positive decimal, so I thought one of the following would work, but the results are not accurate. Sadly b/c OPEN (sch open time) and OPEN_TIME (actual open time) are in diff data tables, I don't think I can create the "variance" or "late" dimension within the load script

COUNT({<Variance = {">0"}>} FACILITY)...........returns 112,146

AGGR(COUNT(Variance > 0), FACILITY)............returns 0

AGGR(SUM(IF(Variance > 0,1,0)), FACILITY).........returns 0