Solved: show table of items data that is missing facts - Qlik Community

dadumas · ‎2015-09-14

My data set includes daily service tickets (the facts) logged against physical equipment items (the dimension) contained in service tickets.

I have requirement to show an equipment items availability report which also includes items in which no tickets have been logged by month. I.E. The items with no tickets against them will represent items that have 100% availability, or 100% "up time". This is easy to do in SQL with a UNION statement across tickets UNION'd with items with no tickets. The resulting output can then be shown as a table or used as an input source.

I am hesitant to concatenate into my case fact table "dummy" records for all items every day in which that item does not have a service ticket logged against it.

dadumas · ‎2015-09-21

I found the answer by adding the following :

sum([Case Count]) + sum({$<[Year]={"*"}>}0)

Has nothing to do with "select all values" (do not use that), but I do leave "Suppress Zero-Values" unchecked. The part after the + is the magic. It somehow forces the intersection of the dimensions with the facts without ignoring selection state.

I then hide that column.

I modified the sample included in this post:

View solution in original post

dclark0699 · ‎2015-09-14

Try checking "Show All Values" in the Dimensions tab for your equipment item field. And then on the presentation tab deselect "Suppress Zero-Values"

kuczynska · ‎2015-09-14

You can try to assign the value to your Nulls, this will allow you to filter for your NULLs. Try the below in your load script:

NullAsValue TicketId; //here type your Ticket Ref field name

Set NullValue = '<NULL>'; //here assign the value you want to display - it's a string in this case

dadumas · ‎2015-09-14

I tried that and did not work, unfortunately.

Let me explain a bit more:

My measure is: ([Total Calendar Minutes] - [Total Minutes to Resolve Ticket]) / [Total Calendar Minutes]

[Total Calendar Minutes] = [Date] X 24 X 60. This gives me the total number of minutes in whatever date range the user selects

[Total Minutes to Resolve Tickets] = sum(MinutesToResolveTicket). This gives me the total number of minutes in which the ticket took to resolve.

dclark0699 · ‎2015-09-14

Could you attach a sample file?

dadumas · ‎2015-09-14

I am not understanding your context here.

My facts are service tickets in which there is a date and an equipment item representing the item logged as needing service repair. The measure is TimeInMinutes to resolve the ticket.

Many equipment items never need repair in a monthly period, so in those cases there are no fact records for those items.

dadumas · ‎2015-09-14

I will try to work up an example with dummy data and attach...

dadumas · ‎2015-09-14

I think I need to create a separate dimension table for items for this to work. Currently items are coming from my fact table. If I bring them in as a separate dimension table and key them to the fact then the "show all value" and the "suppress zero values" might work. I am going to try that.

dclark0699 · ‎2015-09-14

Oh I see. I thought we were working with two tables. Yes, I think my earlier solution will only work if the dimension table is associated in the data model, not if it's part of the fact table

dadumas · ‎2015-09-21

I created a separate dimension table for the items and joined that to the fact. I choose "select all values" for the dimension and de-select "Suppress zero values" in the presentation tab.

This now works when item number is the ONLY dimension in the chart. I.E. It shows all the items regardless of whether they associate to a fact record. However, once I add MonthYear, and Location (and check "select all values" for those), the chart no longer works as expected. Items are location specific. So now, I will concatenate "Location dash item" into a single field and see if this is the work around.