Instead of finding the minimum value, i am trying to figure out how to calculate the 3rd min value and 5th min value partitioned by multiple columns.
Something like this half works, but wondering if there is an easier way to find the nth minimum value?
ifelse(rank([time ASC],[year,key],PRE_FILTER) >= 3, minOver(time,[year,key],PRE_FILTER))
Hello @mozi, wouldn’t you want to check if your rank is equal to 3 OR equal to 5 instead of greater than or equal to 3? It would look like this:
ifelse(rank([time ASC],[year,key],PRE_FILTER) = 3 OR rank([time ASC],[year,key],PRE_FILTER) = 5, minOver(time,[year,key],PRE_FILTER))
That might resolve your issue of the calculation only partially working. I don’t believe there would be a better way to handle this from a calculated field. The only alternative solution I can think of would be adding a rank calculation into your SQL when you ingest the dataset. Then when creating your ifelse statement you would only have to check if the new field is equal to 3 or 5.
Let me know if this helps!
@DylanM - Thanks for the reply. Sorry, I probably wasn’t clear. I would of had another calculation to find the 5th min.
I couldn’t find a way to make sure this was possible with a calculated field so created an a new dataset with custom SQL and joined.
Hello @mozi, I know it isn’t the solution you were hoping for but I am glad that worked. Sometimes changing how you are querying the data in SQL provides an easier solution and ensures you are returning the exact values you are looking for. Good luck building out the rest of your dashboard!