Is there a way to dynamically select the nth row of a table?

tbsheff

I have a table that is part of a drill path. The table is filtered by a bar chart that has a category and numeric value (x). I want to select row (x) from the table after it has been filtered. Is there a way to make this part of the drill path?

ColemenWilson

You can partition on multiple fields for your ranking. It would be helpful if you could maybe do a screen recording where you talk and walk through what you are trying to accomplish. Or I'd be happy to set up a call with you to take a look at it. Once again, I am pretty sure we could find a solution - just not sure exactly what it is you're trying to do. https://calendly.com/cwilsondomo/15min

GrantSmith

.

ColemenWilson

I believe this actually is possible, but I think I need a bit more information about what you are trying to do. You can assign a row number to every row of data in magic ETL, but it will be fixed and wont change from filters in your card or drill path. You can also use a beastmode that will be dynamic and change based on what filters you apply, something like this:

RANK(`some field`) OVER (PARTITION BY `some other field` ORDER BY `another field` DESC)

The problem is, you can't preselect for a certain dynamic row, lets say row 5, because once a filter or drill down occurs, row 5 changes. So the only way to accomplish what I THINK you are trying to do is to give every row a ranking with a partition - the same partition that would occur in the drill down - and then filter the drill path to only return that row.

So for example, you have a card with months on the x-axis and revenue amounts as the y-axis. In magic ETL you do a ranking partitioned on month. Then you setup a drill path with a filter to only show row 5. Each month will have a row 5 associated with that month because of the partition. So if you drill into the January month you would see the 5th row of data with January as the month, and when you click February you would see the 5th row of data associated with February and so on.

MarkSnodgrass

I agree with @ColemenWilson that I think this could be possible, but you need to supply more information as to how you go about determining the "X" row of the table. Your drill path card will automatically be filtered by what you clicked on in your main bar chart, you can have additional filters already applied in your drill path card. Drill path cards can also be built on different datasets than the main card. This may help you as you think through the design of your cards.

tbsheff

I used magic ETL to partition by 'Department' asc and score desc.

Then I used beast mode to create this rank field to dynamically count the rows in a filtered table:

COUNT(Month Year) over (order by Month Year asc, Score desc)

I have 2 filters on the data, 'Department' and 'Score'.

I want to select the row number that is equal to 'Number of Employees' for that department and display 'Month Year'.

I'm still not getting it to dynamically change when the filter changes the rows in the table. Thanks!

ColemenWilson

Okay. You would need 2 fields: 1. A rank 2. Number of Employees and then compare the two in a beastmode and if rank = number of employees then "In" else "out". Rank CAN'T be dynamic in this scenario though because you only want a single row left and that would force the rank of that remaining row to 1 which would then mean rank no longer = number of employees and its an infinite loop. So you need to set the rank in ETL and partition on whatever fields you plan to drill to as I mentioned before. These drill scenarios should be known since you are setting up the card and drill path. So just plan ahead for these drill downs in the ETL. Does that make sense?

tbsheff

Yes, that makes sense. I do have it partitioned on 'Department' with a rank function ordered on 'Month Year' and 'Score'.

My hope is that I could have it filter down to a specific department and a range of scores. Then I want the 'Month Year' that will match 'Number of Employees' for that department.

The issue I run into is that the rank should be changing based on the 'Score' (i.e. I only want scores larger than 7, meaning there less rows in the table and rankings would change). Would I need to partition on 'Score' as well instead of ordering it by 'Score'? (This returns the same result as the calculated field I created) COUNT(Month Year) over (order by Month Year asc, Score desc)

ColemenWilson

You can partition on multiple fields for your ranking. It would be helpful if you could maybe do a screen recording where you talk and walk through what you are trying to accomplish. Or I'd be happy to set up a call with you to take a look at it. Once again, I am pretty sure we could find a solution - just not sure exactly what it is you're trying to do. https://calendly.com/cwilsondomo/15min

tbsheff

I was able to figure this out by creating a calculated running total field and using that as my comparison value. Thanks!