How to do window function in Qlik Sense's table?

[BoXiangWang]

Hi all,

I want to calculate the distinct amount of people within certain groups where their effective_to_date is the latest.

I don't know if there is any window function in QS that I can use as SQL.

Here is an example. The number of each row is duplicated since within each group (rank_senior_comb) there are many ranks. I only want the latest one, which means their effective_to_date is the latest.

I want to have the number of the latest ranks in each group and the total amount should be the same as 2653, which is the amount of people.

Expression: =count({<is_left={0}>} distinct Amount)

For example, 

ID:1

effective_from_date | effective_to_date | rank |

2024-01-15 | 2024-01-30 | BC-NY

2024-01-01 | 2024-01-14 | BC-NI

Then I only want to count BC-NY, since it is the latest one.

[LRuCelver]

FirstSortedValue - chart function | Qlik Sense on Windows Help