Date exhaustivity when filtering data

[Saryk]

Hello,

I'm following up on these threads which give no answer to the same recurring problem :

https://community.qlik.com/t5/QlikView-App-Development/Master-Calendar-enables-all-dates-to-display-...

https://community.qlik.com/t5/Qlik-Sense-App-Development/problem-with-master-calendar-and-filtered-d...

and most importantly, this one (by me) : https://community.qlik.com/t5/New-to-Qlik-Sense/Keep-exhaustive-date-data-through-filter/m-p/1681434...

Is there a way to force all date data (as dimension) to show in a graph, regardless of filters, without creating 0-values for every possible scenario which would (1) increase load times drastically and (2) fill memory with useless data ? I have a master calendat that does that without filters, but as soon as a filter is applied, days, weeks or even months disappear.

I really need an answer to this.

Cheers !

[Saryk]

In other words, Qlik Sense has data, null and no-data which are 3 different things.

When applying a filter, the data is removed. It does not exist, it's not null, it is no-data. Is there any way to force Qlik to fill filtered-out parameters with either data that is 0, or null ?

[Saryk]

Anyone ?

[marcus_sommer]

In some cases you could bypass this intentionally designed behaviour with the use of set analysis which ignored certain selections or overwrite it, for example with something like: sum({1 < Field1 = >} TOTAL Field2). Another method is to apply a default-value if there is none expression-result - this could be tried with: alt(Expression, 0) or maybe rangesum(Expression, 0.00000000001) or similar. A further approach might be to include additionally expressions which have always a result, for example: = 1.

Depending on your data, datamodel and view-requirements one of the mentioned approaches or any combination of them may work - but often they have side-effects which are more or less obvious.

If the above suggestions didn't work you couldn't avoid to create for some data-parts a cartesian product. Quite probably it's not necessary for all data and therefore the efforts to create them (especially by using incremental approaches) and effects on the RAM and the performance might be acceptable (those dummy-data may not increase the RAM extremely because the values within the dimension-table exists already and adding 0 or NULL data do not need much RAM - but like mentioned before it depends on your datamodel and you might need a re-design for it to avoid that those cartesian values are part of a key or a link-table).

Also possible is to create the cartesian data within the UI with synthetic dimensions - but it's definitely slow within the UI, is very complicated and will also have various side-effects.

In conclusion I suggest you to consider if you really need this kind of NULL analysis. I created them already several times on the demand of our sales-people (always if they were in panic that X wasn't sold like expected) but it wasn't used for any action a single time because there were none additionally insights - if X wasn't sold it had the reason it was an old or bad product (mistake from the purchase department) or the sales-people didn't get an appropriate fee (collision of interests between the higher management and the sales-people). No NULL analysis will help if the basics of the sales didn't work.

- Marcus

[Saryk]

Hello,

Thanks for your reply.

The reality is that it is not sales but analyses of application unavailability, so in fact an unavailability of 0 is something important to show. 

Even if it were sales, it would be interesting to see "week 12 : 0" instead of jumping from week 11 to week 13 as if nothing happened. If one is presenting this to a client, they will ask questions as to why week 12 is not appearing.

I will try your suggestions and see how I can implement them ; creating 0 values for every single application for every day is not an option, as the loading goes from about 15 seconds to over 10 minutes, and from ~1000 lines to over 1.5 million.

Regarding remaking the data model, do you have any link that would explain how to dissociate date from data ?

Cheers !

[marcus_sommer]

It was just an example to show that's often not very helpful to display NULL - whereby I wasn't accurate enough in my remarks because Qlik is very powerful to make those NULL visible within the UI through the power of gray:

The-Power-of-Gray-in-QlikView 

But to put NULL within any charts to be able to report/export them is quite difficult to impossible because those data doesn't exists - and it's not only a challenge for Qlik because most of the tools couldn't integrate NULL within their views.

In your current case it seems not be a real problem because it is a quite small dataset - 1.5 millions of records is even with a very small hardware usually enough performant. In regard to your load-times for these records seems 10 minutes rather long - there might be some potential to optimize the direct loading and shouldn't it be enough you could apply an incremental approach.

- Marcus