Last 10 ids based on another field

Question & Answer
, ,
1

Hi I want to filter a report to have only the last 10 appointment ids by state, type, size.
so I have the below where I have calculated the Max, Min and Avg but over all my appointment IDs.
I want the max, min and avg fields to have the values based only on the last 10 appointment IDs per State, Expense Category, Type and size.
So i.e. I want the max Approved amount for

  • the last 10 appointments of state A, expense 1, type 1 and size v1
  • the last 10 appointments of state A, expense 1, type 1 and size v2
    etc

image
image818×142 5.01 KB

2

Hello @pantelis, are you wanting to show 2 different visuals and one is filtered to State A, expense 1, type 1, and size v1 and a 2nd that has size v2? Or do you want to show last 10 appointments for size v1 and 10 for v2 in the same table visual?

If it is a single visual, I would apply the filters for each those options directly on the visual. Then, utilizing denseRank, you can filter to the top 10. Is there a date field in your dataset that we can use to rank? If so, this example should help:

denseRank([{Date} DESC], [], PRE_AGG)

If the visual is already filtered to your required specifications, you can leave the partition field blank. Then you can apply a filter to the visual to only show values less than or equal to 10.

Now, if you need latest 10 appointments for both categories, you will need to add a partition field in the denseRank function:

denseRank([{Date} DESC], [{Size}], PRE_AGG)

Lastly, if your Max Approved, Min Approved, and Avg Approved calculations are LAC-W aggregations and contain the PRE_AGG calculation level, they will all be calculated after the filter is applied to the visual. I’ll attach some documentation below, but let me know if that helps!

One last note, when posting about calculated fields and authoring analyses, can you utilize the author tags rather than developer tags? That will ensure you are more likely to receive a response from the correct QuickSight expert. Thank you!

1 Like
3

Hi @DylanM

I am not sure I understood and I don’t know if I explained my situation correctly so I will elaborate with the below.

I have 1000 appointments and 100 of them are for State A, Expense 1, Type 1 and Size V1.
I have calculated the Max approved as below.
This field takes into account all 100 appointments for that combination and gets me the max approved amount.
I don’t want the field to calculate the max among all 100 but among only the last 10 appointments of combination State A, Expense 1, Type 1 and Size V1.

How to take into account in my calculations only the last 10 appointments per combination?

maxOver({Approved Amount},

[{State},{Expense},{Type},{Size}],

PRE_AGG)
4

Hi @DylanM

Do not mind my previous reply. By trial and error I understood you reply and found my solution with

denseRank([{Date} DESC], [{Size}], PRE_AGG) .

I ranked my ids based on the partitions I needed and in my calculated field I added the below condition

ifelse({dense_rank} < 11
maxOver({Approved Amount},

[{State},{Expense},{Type},{Size}],

PRE_AGG)

Thanks

1 Like