Selecting the last 30 rows in filtered data

Rob29 — Mon, 13 Sep 2021 12:25:58 GMT

Dear Qlik specialists,

Maybe you can set me on the right track in solving the following problem.

I have a dataset, containing (.. a lot, but for this example..) the following fields:
- date-timefield (MyDateTime) (not unique),
- ranknumber (calculated in the database SQL) (myRankNumber) ,
- value (MyValue),
- dimension to distinquish null-values (MyNullValue),
- dimension used for filtering the data (MyDimension)

In a Combochart, I am showing MyValue against MyRank on the x-axis. I could not use the datetime because that yields a timeline, and the datapoints are not organized on a regular time base, and I did not want gaps or stretched lines against a time-axis.
So the RankNumber was generated in the SQL to be used as a dimension, to be able to connect all datapoints on the same distances on an x-axis.

Furthermore are there lines that don't have a Value (null)
To be able to exclude these lines a new dimension (MyNullValues) has been made in the SQL. In Qlik Sense cannot be filtered on NULL values.

What I did sofar is showing the graph for all data exept for the Null values. I did this by applying an If statement in the axis definition (Dimensions of the ComboChart).
IF ( MyNullValues = 'there is a value', MyRank)

That works out fine.
I sorted the data on MyRank, and that worked out fine as well.

Furthermore, I am filtering the dashboard (the combochart is one of the vizzes on that dashboard) by MyDimension , which has effect on all vizzes on the dashboard, including this combochart.

Due to the filter by MyDimension the numbers in MyRank are not consecutive numbers anymore.
Now, I want to show only the last 30 datapoints in my combochart.
The problem is that this combochart also contains calculated data like average and stddev related calculations. (upper and lower control limits)
So the last 30 datapoints should not only be shown in the visible part of the graph, but als well used to calculate those average and stdev based calculations.

I have tried to add an extra condition in the if statement for the axis, but that doesn't work.
IF ( MyNullValues = 'there is a value' and (Rank(Max(aggr(Rank(MyRank),MyRank))) > Rank(Max(aggr(Rank(MyRank),MyRank)))-30 ) , MyRank)
// I have Ranked the Rank function because there are gaps in the original MyRank

I have tried similar set analysis constructions in the Dimension and in the Measures, but still I don't get the required outcome.

Maybe I am thinking far to difficult, I don't know.
So I hope there is someone out there that can give me a push in the right direction.

Thanks in advance!
Rob

topic Selecting the last 30 rows in filtered data in App Development

Selecting the last 30 rows in filtered data