Adding an expression field affects rowcount in grid chart object

[youcantryreachingme]

I'm debugging an application containing a chart object which is of "grid" type. End users have reported that it does not filter correctly. They select a date range yet the chart fails to filter records based on that date range. This behaviour started only recently. When I try the same steps in a backed-up copy of the document from a few weeks ago, it filters correctly.

Setting aside for a moment the possibility of some development change in the interim period introducing a bug, there is some strange behaviour that I don't think can be explained.

The chart object contains 20 dimensions and 31 expressions. Three of the expressions refer to the date column that should be used for filtering the rows. Each of these expressions  is a sum with the following format:

=if([Total Costs]<>0 or [Total Invoices] <>0,
sum({$<InvoiceCostDate.Date=,InvoiceCostDate.LastFinancialYearFlag=,InvoiceCostDate.LastYearFlag=,InvoiceCostDate.LastQuarterFlag=,InvoiceCostDate.LastMonthFlag=>}TotalCosts))

If I remove all 3 expressions, the chart correctly filters its rows based on my date range selection. If I add any 1 expression back to the chart, it fails to filter the rows. (This behaviour does not occur on the "working" copy of the document - regardless of the presence of the expressions, the rows correctly filter based on my selection).

What I don't understand is that I wouldn't expect an expression to affect the chart's row count. Surely an expression is there to calculate a value based on a given row's other values? Why does having this expression in the chart appear to break the filtering?

The field I am filtering on is InvoiceCostDate.Date and specifying a date range of 3 months.

When the chart is correctly filtering, there are about 2,000 rows. When it fails to filter, there are about 330,000 rows.

Are we pushing QlikView to its limits with data files this big (circa 270MB)? (Why should it work 6 weeks ago, but not now? If this is a bug, introduced via changing the ETL that prepares the data - then the root cause should be data related ... but then why does the filtering work when I remove an expression from the chart?)

[marcus_sommer]

I doubt that there is any bug on the Qlik side or that it hit any limitations and also that the applications has really 270 GB. Further without considering the datamodel and the loaded data and their quality it's not possible to justify if the charts and their calculation are working properly or not.

In your case you used set analysis within the expression and adjust respectively overwrite with it the selections in your application, for example:

... InvoiceCostDate.Date= ...

caused an ignoring of any selections within this field.

- Marcus

[youcantryreachingme]

Hi Marcus,

Thank you for sharing your thoughts.

When I took on this role, the prior BI Analyst explained to me - regarding a different QV application - that at one point the chart object began showing results that were in error. Although he removed problem columns and re-created them, it continued to have errors. His solution was to build a new chart object with fewer columns. That document was used by NPrinting, and so for some report mailouts he would source data from the old chart object and for some reports he would source from the new chart object. His opinion was that QV could not cope with the size and complexity of the data and chart. Although that was a different document, it's in the back of my mind because the document I'm working with is very similar in size and design to the one where he found behavioural issues he could not fix.

You said you doubt the document really has 270MB. The document file is 270+MB on disk - this is what I mean.

I am not clear what your recommendation is, in the last part of your reply. Do you mean that I should replace parts of the expression with hard-coded values that match the values I would choose in my selection, to see if it correctly filters (ie. when the values are hard-coded)?

You wrote that "InvoiceCostDate.Date= ... caused an ignoring of any selections within this field" - yes, this is true. However a backed-up copy of the file that is less than 2 weeks older does not exhibit this behaviour even though the expression's definition is identical. This leads me to wonder whether:

1) There is a logical explanation - for example, the difference is data-related and somehow there is new data in that last two weeks that is affecting that filter ... but like you said, it is difficult to understand how this could happen (assuming QV is running "logically" - ie. without any faults)

2) There is a corruption - for example, QV cannot handle the file size, or there are too many dimensions and expressions in the chart object, or too many chart objects and tabs, etc, in the document, such that memory faults occur, or the machine on which it is running has faulty memory.

EDIT:  However, I believe it's unlikely to be a memory hardware issue because when I transfer the affected document from the QV server to my local machine and test there, I experience the same issue. This leaves me with the conclusion that either the file has become corrupted (and remains corrupted, even though we regularly reload the data) or QV is not coping with something like file size or complexity.

I would love to hear others' thoughts, however - either on how to debug, or other possible causes.

Chris.

[marcus_sommer]

There aren't not many limitations and/or bugs within the table-charts in Qlik. One is (technically) that a field could have max. 2 trillions distinct fieldvalues. Further there are (intentionally) restrictions to the max. RAM size of an object and/or the calculation times (timeouts). In earlier releases there was various bugs if tables has a lot of columns (30+ dimensions/expressions) - I don't know if they are further exists.

From your description it didn't sound that this may related to your case.

If a working object doesn't work properly anymore it's most often caused from changes within data and/or the datamodel. Corrupt objects are very rare and without changes on the object-properties and/or using prj-folders and/or it is a server-object and the shared-file is corrupt and/or switching the application between different releases very unlikely.

Again I must ask for the disc/ram size of the application - applications with more as 270 GB are probably very seldom, their require quite large server environments and usually their are only manageable if the are well designed and optimized. Now you says it's the same if you do it locally ... therefore I think the application must be smaller, maybe 270 MB?

I think you will need to look into the datamodel and check if there was any change and to compare how the data might have changed.

- Marcus

[youcantryreachingme]

Hi Marcus,

Sorry, you're quite right - I did mean 270MB, not GB.

You wrote "In earlier releases there was various bugs if tables has a lot of columns (30+ dimensions/expressions) - I don't know if they are further exists." The chart has 20 dimensions + 31 expressions = 51 columns. Do you know where I can read more about the bugs you mention, and the QV versions affected?

Regards,

Chris.

[marcus_sommer]

I think it won't be easy to find those postings. Easier will be to split the table into two or or more parts and to check the results there. In general it's not recommended to create such wide tables - nobody could really work with them in Qlik so that the only aim could be to export them into any other tool.

In this regard it should be considered if really all fields and measures are needed and if they could be better created within multiple (maybe more specialized) objects and/or within the script. In your case one of the 31 expressions might now work different and prevent your expected record-reduction by overwriting the selection or anything changed within the data and a certain filter didn't work anymore.

- Marcus

[Brett_Bleess]

Chris, I was able to find one article I actually wrote up, not sure if it is truly applicable in your case, but it does follow what Marcus was stating...

Straight Table column limit in Ajax client 

Regards,
Brett

[youcantryreachingme]

Thanks Marcus and Brett.

I forgot to mention - I ran another test: create a new chart object, include one or two dimensions - the primary key / unique value, and the date field being selected - then add the Expression column I described in the original post ... and that new chart fails to filter (even though there are at most 3 columns).

The expression was this:

=if([Total Costs]<>0 or [Total Invoices] <>0,
sum({$<InvoiceCostDate.Date=,InvoiceCostDate.LastFinancialYearFlag=,InvoiceCostDate.LastYearFlag=,InvoiceCostDate.LastQuarterFlag=,InvoiceCostDate.LastMonthFlag=>}TotalCosts))

I found another post on the forums where someone asked for a technique to actively ignore a selection in a given expression, here: https://community.qlik.com/t5/QlikView-App-Development/Filters/m-p/1142973

That solution advised:

"Change your expressions to add set analysis to ignore selection in Terminal Name

=Sum({<TerminalName = >}Volume)"

and the original poster said this worked. I have read the QV documentation on set analysis but I am not following how this functionality works. I can see, however, that having TerminalName within the curly braces and angle brackets, etc, resulted in that expression ignoring any selections for TerminalName.

In my case I have an expression using the same syntax and also ignoring the selection (but it did not always do so, and an older backup does not do this).

Might this "set analysis" be the cause in my case? If so, why doesn't it restrict filtering in the older backup document?

[marcus_sommer]

Like always - it depends. Set analysis could be regarded as a selection but this means also it will only work like expected if the datamodel has the proper associations between the in the chart used fields (in dimensions as well as in expression). This means if the used fields are not correct - in the sense of the evaluation - associated and/or the datamodel contains synthetic keys or even circular loops the intended views won't work.

This kind of check must always be the first because you won't never find and/or cure issues within your UI which are caused from the datamodel.

Beside this only measures which are defined within a set analysis expression will have an impact on the considered selections - means a construct like: if(AnyFieldsOrExpressions = true(), SetAnalysisExpression) will only partly influence the selections. This means you may need to add the set analysis to all branches and if it are nested aggregations also to the inner- and the outer-aggregations. And this is true for all used expressions and also the dimensions if they are created per expression or variable.

The above mentioned logic is the "default" logic by hiding NULL values in the dimensions (in tab dimension) and in the expressions (tab presentation). If the displaying of NULL values are enabled the behaviour might change.

Another aspect which will have an impact are alternate states - if your charts and your listboxes are in different states they remain independent to eachother by any selections in the other state.

- Marcus

[Brett_Bleess]

Hey Chris, below is a great link for Set Analysis, not sure you found this one, as I am guessing you were likely reading the Help.  The below link is the base link, but there are some other posts at the bottom of it to other related Design Blog posts, hopefully this may help a bit more in addition to Marcus' latest post.

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

Regards,
Brett