How to get the most frequent branch for every driver?

Question & Answer
, , ,
1

I’m trying to create a table that shows every driver, their number of deliveries and their most delivered from branch.

Here is what my data look like:

Driver ID Branch Date
1111111 A 2024-Jul-01
1111111 A 2024-Jul-01
1111111 B 2024-Jul-02
1111111 C 2024-Jul-04
2222222 A 2024-Jul-04
2222222 G 2024-Jul-06

I want to create the following:

Driver ID Number of Deliveries Most / Last Frequent Branch
1111111 4 A
2222222 2 G

I want to show the most frequent branch for every driver, if there is a tie then show the latest branch

2

any solution to this?

I have tried this expression:

ifelse(countOver(Branch, [{DRIVER_ID}, Branch],PRE_FILTER) = maxOver(countOver(Branch, [{DRIVER_ID}, Branch],PRE_FILTER), [{DRIVER_ID}],PRE_FILTER), Branch, NULL)

but this returns two values per driver, NULL and the max branch by count. what if I want to return the Max branch by count infront of all rows belonging to a driver?

3

Hi @Ali_B,
Are you looking for something like this?
Check Here :
sample solution 32854

I used filter to limit the rows instead of calculated field to fetch the branch id.

Thanks,
Prantika

1 Like
4

Thanks prantika,

this seems to work only when I modify all other calculations in the table to Pre_Filter which makes the users not able to filter by date and other dimensions.

Can this be solved using the FirstValue() function?

5

Hi @Ali_B, did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

6