Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.