topic Re: AGGR in expression different when loading from qvd in App Development

AGGR in expression different when loading from qvd

bc5891 — Tue, 21 Dec 2021 22:04:53 GMT

Hi,

I have come across some odd behaviour and wondering if anyone experienced similar. I have created some logic in a table and then using a graph I have a front end expression which calculates the remaining amounts across the time groups.

Once I checked the logic I stored it into a qvd and dropped the original table. I loaded from the qvd and the chart is different, even though it uses the same expressions and the number of values in the qvd are the same as the original. The actual values in the qvd are the same but the expression I am using does not work the same way when loading from a qvd.

the expression is as follows:

AGGR(
sum(total <SCH_EVENT_TYPE>{<SCH_YEAR_GROUP = {'Within Yr'}>}SCH_AMOUNT)/1000000
-
RangeSum(Above(sum(SCH_AMOUNT)/1000000, 0, RowNo()-1))
, SCH_EVENT_TYPE, SCH_YEAR_GROUP)

in this I am taking the full value of the initial amount at the first time interval, and then in the next time intervals I only display the remaining values, i.e. the number should always decrease over time. When loading the exact same thing from the qvd though I get the following chart:

Question is: has anyone come across this sort of behaviour before, is it a bug?

I cannot share the application but can assure you that the only difference between the two charts is that one is from a qvd and the other is not, both have the same number of rows and fields.

Thanks

Re: AGGR in expression different when loading from qvd

QFabian — Tue, 10 Nov 2020 14:49:41 GMT

try sending this message to the qlik bug group

https://community.qlik.com/t5/QlikBug/gh-p/qlikbug

Re: AGGR in expression different when loading from qvd

marcus_sommer — Wed, 11 Nov 2020 09:56:21 GMT

The order of your values may change by your method. This is caused through the way Qlik handles and stored the data (a symbol-table for each field with only distinct values and a data-table which only contains binary pointer to the symbol-table).

Because of the fact that you are using an interrecord-function and rowno() within your expression it may impact the result. Further there is an issue with your expression because the aggr() missed an outer aggregation and behaved here like a (calculated) dimension.

- Marcus

Re: AGGR in expression different when loading from qvd

bc5891 — Wed, 11 Nov 2020 10:18:58 GMT

Thank you for your response Marcus. So in essence the loading from qvd changes the ordering which changes the output of the chart? I would need to load resident from the qvd and replicate the initial ordering?

In terms of the AGGR function, could you expand a little please, should there be an additional AGGR then?

Re: AGGR in expression different when loading from qvd

marcus_sommer — Wed, 11 Nov 2020 12:52:06 GMT

Not only the storing could change the order else also the new loading. If a field is loaded Qlik checked for each value if it already exists within the symbol-table - if not it adds the value there and adds 1 to the max. value of the binary pointer and put this pointer value in the record of the data-table but if this value already exists the system-table won't be touched unless to pick the appropriate pointer-value to put it into the data-table, too. Therefore the order of the field-values depends on the load-order within the whole script and it's not related to the tables in which they were loaded.

With an outer aggregation was something like the following meant:

sum(aggr(YourExpression))

- Marcus