Suppose I have a straing tables with several dimensions and expressions. To make it simple, let's assume that we have date, item and sales quantity. I would like to show combinations of dates and items that had no sales. The problem is that the source table (e.g. - a fact table in the DB) doesn't hold a record for items and dates that had no sales. So how can I isolate those items in QlikView? the suppress zero values or missing cannot help here because those values are not zeros nor missing - They simply not exist...
Unfortunately, in my opinion anyway, QlikView isn't very good at displaying missing data.
A common approach is to create dimension tables disconnected from your main data. See attached. We create a separate table of all of the dates, and a separate table of all of your items. Then we create a chart with these new fields as your dimensions rather than your real date and item. Then we use some expression to idenfity the missing data, such as sum(if("Date"="All Date" and "Item"="All Item",Sales))=0. Your chart will then show only the missing data. Or you can just remove the "=0" to show both present and missing data.
There are some serious downsides, though. One of the biggest in practice can be performance. This approach is very slow on large data sets because it's replacing QlikView's associative logic with an IF statement being evaluated over and over and over. Another problem is that selections in the "All Date" field, for instance, won't affect charts using "Date", and vice versa.
Hey, Qlikview for sure is not good at displaying missing data...
We have same problem here. We want to show both, missing and present to get to a printed report e.g. P&L. I tried with Flag and big amount of dummy-data. Set formula "MIN( {1<schema= {'$(printschema)'} >} schemaflag)". Now dummy-data in team with schemaflag shows all lines within my printschema. To hide this expression i used a macro. Lot of work only to show some otherwise missing lines. 😞
I'll try your solution today. Perhaps there is less work to do. Performance should be worse...
_________________
OK, your solution seem to work just fine... but - performance is horror. For my needs this is impossible. 😞 ! Would be way better to work with your idea - but never on large datasets - you said so... 😞 ! I've to stay with my very bulky way of teaching qlikview to do what i want it to.
Your application is really interesting!!! I tried in a similar way but the chart is not working because I have very huge amount of data. When I partially load it with 1000-10,000 rows then I get my output. But really don't understand how to overcome on this situation. One more thing in your sample you have 3 separate tables 1) Real Data ( contains all required fields) 2) All Dates 3) All Items. But I don't have Real Data in the Same table, instead I have two tables which I outer join and made it a single table (Table1: Sales, Table2: Store Codes). My Sales Table Is having Date field which is Sales Date and Store Code doesn't contain Dates as it's a master table. And I want to show list of Store codes with Zero Sales. I created a separate Date table from my Sales Table. It will be great if you can put your comments on this.
