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

[youcantryreachingme]

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.

[youcantryreachingme]

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([Total Costs]<>0 or [Total Invoices] <>0,
sum({$<InvoiceCostDate.Date=,InvoiceCostDate.LastFinancialYearFlag=,InvoiceCostDate.LastYearFlag=,InvoiceCostDate.LastQuarterFlag=,InvoiceCostDate.LastMonthFlag=>}EstimatedTotalCost))

• 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.