Count NULL values with set analysis for other criteria

[drew61199]

I'm developing a dashboard for vehicle utilization. Below is a snip of the data in a table. I need to count the days w/o usage, but also where the vehicle was NOT indicated as "In Shop". So in the example below, it wasn't used 4 days, but was in shop 2 days, so my formula needs to show the other 2. 

Below is my formula. Without the section in red, I get 4, so it's properly counting days without utiliztion. I added the set analysis that I thought would work, but instead, the count is 0. I tried with "" and simply a 1 (no marks at all). What am I missing? 

[sidhiq91]

@drew61199  Please try the below expression:

Count({<AVUS_Date={"=Len(trim(Utilization))=0"}, In_shop={"=Null()"}>}AVUS_Date)

or

Count({<AVUS_Date={"=Len(trim(Utilization))=0"}, In_shop-={"=len(trim(In_shop))>0"}>}AVUS_Date)

[Mark_Little]

Hi @drew61199 

@sidhiq91  looks like it should do the trick, another options is to look at NULL as Value script functions or just flagging the field in script something like

IF(LEN(TRIM(In_shop)) = 0, 1,0) AS In_shop_null

[drew61199]

Sadly none of these suggestions are working. I decided to do a CASE WHEN statement in the load to convert the 0,1 to NO, YES, but that doesn't seem to help either. I need it to display 1 for the below example (7/23). If I remove the In_Shop portion of set analysis, I get 3 (which would be correct if I weren't' trying to exclude the in shop days). 

It just dawned on me why this may not be working, but I'm not sure how to find the solution (I'd think In_Shop-={"YES"}, but that returns a 0). So the dataset only contains days where a vehicle was utilized (or indicated in shop). A LOT (like 50%) of the time, a vehicle has no data (example below is an anomaly), so in order to properly calculate non-utilization, I created a table by cartesian join on all vehicle numbers and all calendar dates and then joined in the utilization data. So 7/23 below, truly has "no data"....so there is not an In_Shop indication coming from the database. Now I would assume QLIK would recognize the NULL, but does not seem to. Any other ideas? 

[drew61199]

UPDATE2: Took a completely diff approach. This seems to be working. Ideally I don't want the subtraction part, but not sure I can set analysis in an "aggregated if" statement

Count(AGGR(IF(SUM(Utilization)=0,vehicle),vehicle,AVUS_Date))-Count({<In_Shop={"YES"}>}AVUS_Date)

UDPATE: So the very bottom may or may not work....but I think I have another issue. When I selected two vehicles (one with 22 days of non-utilization and the one in the above example with 1....I would expect "23"....instead I got "9". I then removed the subtraction part and kept just the COUNT....I now get 11. So on an individual vehicle basis, all formulas work, but I'm afraid when I roll up to a facility or district, it definitely isn't going to

Count({<AVUS_Date={"=Len(Trim(Utilization))=0"}>}AVUS_Date)

FYI: This works....but I'm afraid it may break when I do rollups for facilities, etc

Count({<AVUS_Date={"=Len(Trim(Utilization))=0"}>}AVUS_Date)-Count({<In_Shop={"YES"}>}AVUS_Date)

[Digvijay_Singh]

If possible, please share sample data and expected output, its difficult to frame a perfect expression without trying few things.

[drew61199]

Small sample attached. The data is only populated for days when someone signs in to use a vehicle. So if 7/1, 7/2, 7/3 are signed in, but 7/4-7/31 are not, then we only have 3 days of data. I need to show the other 28 days as "not utilized". This is why I cartesian joined dates and distinct vehicle numbers so I had a 1=1 table.....and then tied utilization into that. 

This statement works in the frontend, but it is VERY slow. I'm only loading 1 month, but it's millions of lines (due to cartesian). The AVUS_Date is name I gave 'eventenddate' field and Utilization is simply endodometer-beginodometer (both named in load statement).  I'd love to be able to do something in the load statement so the dashboard is faster, but could not get either of the below to work (broke the two calcs apart and tried loading individually with the appropriate field names - ie - eventenddate), but I assume the COUNT(AGGR(IF is something load won 't let you use b/c syntax turns red starting at IF

Count(AGGR(IF(SUM(Utilization)=0,vehicle),vehicle,AVUS_Date)) - Count({<In_Shop={"YES"}>}AVUS_Date)