Can I freeze RANK then filter out?

Question & Answer
1

I am performing “RANK” operation on a field which is mapped with another field.

Fields involved:

  • “id” (Incremental record number for every entry/case)
  • “case_check_id” (Unique value for every entry/case)
  • “status” (Status of the entry/case)

To find the latest status of a case.

We need to perform rank operation on it like what is the max(id) per case_check_id.

Once we get the max(id) its related status is the current status for that case.

Till here we are good, its implemented.

Now there are 15 statuses all total, which are divided in to two slots:

1- With Scope
2- Without Scope

Things fall apart when we separate out status respective of its scope.

Requirement is - Rank should be calculated irrespective of scope.

But when we calculate rank, it is getting calculated twice once for With Scope and next for Without Scope.

Additionally RANK is calculated with in a date range which is provided as a onscreen filter.

2

Are you using PRE_FILTER? If you don’t want the rank to change when you filter by status, you need to use PRE_FILTER.
rank([{id} DESC], [{case_check_id}], PRE_FILTER)

What complicates things is that you want your date range filter to be factored in when calculating the rank but not your status filter.

You can try this but I’m not sure if would work with a date range filter:
rank([{id} DESC], [{case_check_id}, {date}], PRE_FILTER)

1 Like
3

@ashok1121 - Did you try using PRE_FILTER as suggested by David. Looks like your aggregation is happening at the SLOT level based on the scenario you mentioned. Please let us know if this issue is resolved.

Thanks you!

1 Like
4

I already have applied the same yet, doesnt meet the expectation.

5

No its not working, with the above suggestion

6

Can you upload a screenshot showing some sample data and the result that you’re getting?

2 Likes