Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
bc5891
Contributor III
Contributor III

AGGR in expression different when loading from qvd

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.

 

bc5891_0-1605016616470.png

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:

 

bc5891_1-1605016831257.png

 

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

 

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post

4 Replies
QFabian
Specialist II
Specialist II

try sending this message to the qlik bug group

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

 

QFabian
marcus_sommer
MVP & Luminary
MVP & Luminary

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

bc5891
Contributor III
Contributor III
Author

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?

marcus_sommer
MVP & Luminary
MVP & Luminary

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

View solution in original post