topic Re: Adding an expression field affects rowcount in grid chart object in QlikView

Adding an expression field affects rowcount in grid chart object

youcantryreachingme — Mon, 21 Oct 2019 22:38:56 GMT

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?)

Re: Adding an expression field affects rowcount in grid chart object

marcus_sommer — Fri, 18 Oct 2019 08:35:59 GMT

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

Re: Adding an expression field affects rowcount in grid chart object

youcantryreachingme — Mon, 21 Oct 2019 22:39:26 GMT

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.

Re: Adding an expression field affects rowcount in grid chart object

marcus_sommer — Mon, 21 Oct 2019 10:35:48 GMT

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

Re: Adding an expression field affects rowcount in grid chart object

youcantryreachingme — Mon, 21 Oct 2019 22:41:53 GMT

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.

Re: Adding an expression field affects rowcount in grid chart object

marcus_sommer — Tue, 22 Oct 2019 10:20:09 GMT

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

Re: Adding an expression field affects rowcount in grid chart object

Brett_Bleess — Wed, 23 Oct 2019 19:33:39 GMT

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

Re: Adding an expression field affects rowcount in grid chart object

youcantryreachingme — Fri, 25 Oct 2019 02:08:04 GMT

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?

Re: Adding an expression field affects rowcount in grid chart object

marcus_sommer — Fri, 25 Oct 2019 07:30:04 GMT

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

Re: Adding an expression field affects rowcount in grid chart object

Brett_Bleess — Mon, 28 Oct 2019 20:00:08 GMT

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

Re: Adding an expression field affects rowcount in grid chart object

youcantryreachingme — Mon, 28 Oct 2019 21:50:10 GMT

Thanks Marcus and Brett.

I've been away from the QV dev for a few days but I will look into the information provided and investigate further. I'll post back here in due course.

Cheers.

Re: Adding an expression field affects rowcount in grid chart object

youcantryreachingme — Mon, 25 Nov 2019 23:25:38 GMT

Ok, I'm back at this. Let me start from the top with a screenshot. This shows:

"Current Selections" box in the QV document shows the filters currently applied. Note the InvoiceCostDate.Date field is set for invoices dated between 1st and 31st July this year.
In the "Export Master Report to Excel" grid chart below this you see the column labelled "Invoice/Cost Date" showing date values well outside the filter range.
On the left we have part of the Properties dialogue box for that lower grid chart. It shows the dimension InvoiceCostDate.Date has the label "Invoice/Cost date" as we see in the chart. That is, this should be the field being evaluated by the filter
When hitting the Edit... button for that Used Dimension, it opens the expression editor at the bottom which confirms no funky stuff is going on with formulas here.

All fine and dandy. Here is the screenshot:

Now, as mentioned in my original post, the grid chart object contains some Expression fields too. Three of them refer to the InvoiceCostDate.Date dimension. Here is an example of one of these formulas:

If I remove all 3 such Expressions, the chart filters as expected.
If I add back any 1 of these Expressions, the chart fails to filter as expected.
This behaviour happens on my local laptop within the QV application dev environment and it happens on the deployed document via the web interface to QV
This behaviour does not happen with a copy of the document backed-up on 4 September. It does happen with 3 copies I've checked which were backed up on 12 September or later.
I can see no changes made to that chart or even document file between 4 and 12 September.

Help?

😮

As a side note (perhaps?) I have no idea why the presence of an expression field should alter the rowcount of the chart - that's not how I understand expressions to work. However QV is a different beast, and perhaps the set analysis in these expression is affecting rowcount? (That's something I don't understand, but even then - why no issue on 4 September?)

Last note - there were some changes to the ETL in another file in the reload process. I checked those changes before and could see no relationship to this issue, but I will do that again anyway because it seem the only logical explanation for a change in system behaviour between 4 and 12 Sept.