Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Filter Data by last values

Hi experts!

I would like to filter all my data by the last 100 values of a field, how can i do it?

Explaining, i have a field Date, and a filed DeltaDate (today - date). i would like that all my sheet is filtered, when i need it, with the last 100 values (last 100 dates). Only using this number.

How can i do it? It would be nice to set a variable connected to a button, but i have no clue on the variable definition.

Thankyou!

7 Replies
sunny_talwar

May be create a counter field

RowNo() as Counter

and then use select in field on Counter with the following search string:

='>=' & (Max({1} Counter) - 100)

Not applicable
Author

Hello Sunny! Thank you for your reply.

Just to be clear, should I insert in the load script the line: RowNo() as Counter?

Whith which specification? Could you please be more clear? I am pretty new to Qlik Sense

Thank you very much again!

sunny_talwar

Didn't realize this was Qlik Sense... I responded thinking this was QlikView... sorry about that

Not applicable
Author

No worries! Do you know how to do that using Qlik Sense?

sunny_talwar

You want to remove all except the last 100 in the script or you are looking to filter on the front end?

sunny_talwar

For front end, you might be able to use an extension for select in field: Select in field extension - Qlik sense | Qlik Community

the logic will be the same where you will create  RowNo() as Counter in the script and then select everything greater than Max({1}Counter) - 100

Not applicable
Author

ok I will be more clear.

  • i have a sheet with several fields and values
  • i have already in place some filter panel to filter the data in the sheet (therefore the count of the dates is not fixed, it depends also an selected filters)
  • I want the possibility to filter all the data with the 100 most recent values (I have both the fields date and delta date)
  • the most recent 100 are not fixed, due to the fact that selecting some filters it could changed.

I had two ideas:

  1. add a field in the load script with a counter, and then with a variable select the most recent 100 (not counter<100, because filters may change which are the most recent ones). but i have no ideas on how write the load script and the variable
  2. add a variable that select (<100) valueas that i have in a TABLE, that is changing depending on filters (I added as first column rowno(total).

Ideas?

Thank you again