Find max of Rangesum

[#1]

Hello everybody,

I have a Problem and I have tried different things but I really do not understand why my expression is not working. I have a timetable and count all tasks which happen at one timestamp.

Count(DISTINCT If([Departure] = Timestamp, Id)) - Count(DISTINCT If([Arrival] = Timestamp, Id))

To get the accumulative sum at each timestamp i use the following expression

Rangesum(Above(Aggr(Count(DISTINCT If([Departure] = Timestamp, Id)) - Count(DISTINCT If([Arrival] = Timestamp, Id)), Timestamp), 0, RowNo()))

In a textbox i want to display the maximum of tasks which happen at the same time. For any reason it does weird things and does not show the correct number (which may be 2 in the attached file). I found this answer (Max Value in rangesum(above(Mark, 0, rowno())) | Qlik Community ), but for me it does not work...

So please help me!

[sunny_talwar]

If you have QlikView 12 on your computer, try this expression:

=Max(Aggr(Rangesum(Above(Aggr(Count(DISTINCT If([Departure] = Timestamp, Id)) - Count(DISTINCT If([Arrival] = Timestamp, Id)), (Timestamp, (NUMERIC))), 0, RowNo())), (Timestamp, (NUMERIC))))

[#3]

I only have QlikView 11.20 (SR 10), so I get the error  "')' expected". Do you have any ideas what's wrong with my expression?

[sunny_talwar]

This part of the expression is a new feature which is only available in QV12

=Max(Aggr(Rangesum(Above(Aggr(Count(DISTINCT If([Departure] = Timestamp, Id)) - Count(DISTINCT If([Arrival] = Timestamp, Id)), (Timestamp, (NUMERIC))), 0, RowNo())), (Timestamp, (NUMERIC))))

[swuehl]

The aggr() dimension values are sorted by load order, not in chronological, numeric order.

Hence the chart inter record functions like above() address not the 'correct' timestamp.

You would need to create the timestamp field values in chronological load order.

See attached.

[#6]

Does it matter where i do the sorting? In my production application after loading this data there follow lots of different datasets. And some of them are joined to this table. So i do the sorting best after all modifications?

[swuehl · Accepted solution]

You need to create the load order of the field you are using in the aggr() dimension in the correct, chronological order.

The load order is determined by the first value appearance in the script execution.

So you need to create a field load with the correct order at the beginning of your script (or at least before you load other values of that field), not at the end.

Example:

If your field would contain sequential integer numbers, 1 to 100, but the load script would load them in random order, you could create a dummy table:

DUMMY:

LOAD recno() as FIELD

AUTOGENERATE 100;

Then load your data:

LOAD FIELD,

          ...

FROM ....;

At the end, you could even drop your first table, the load order will still be kept:

DROP TABLE DUMMY;

Now the field should be in correct load order. Check this by creating a list box for FIELD and set sort order to load order asc.

Hope this helps,

Stefan