As you can see in the doc embedded, I have found the from the list with dates the biggest streak.
Q: How can I find the second, third,â€¦ ranked score?
As you can see in the doc embedded, I have found the from the list with dates the biggest streak.
Q: How can I find the second, third,â€¦ ranked score?
Hi @Jean_Pierre_Traets
Iâ€™m not sure iâ€™ve understood correctly your â€śproblemâ€ť, surely iâ€™ve done similiar algorithms in the past and those have made me crazy, literally, dates in coda do what they want, and sorted elements itâ€™s not always sorted as one could imagine
Can you please try to explain better the situation? Iâ€™ve got the â€śdays in a rowâ€ť and the bigger is higher principle, so thanks to that you know which one is the first, but you also know what are the other in order or not?
In that case if the table upon itâ€™s valid i think itâ€™s easy to recover the other value
Let me know, this sound interesting
Dear @Mario, thanks for your time and effort to support.
The first longst streak is 22 days, thatâ€™s rather simple, as you mentioned.
My challenge is how to calculate the second, third longest streak with a formula, in the â€śstreak tableâ€ť, I have put the results manualy to show what result Iâ€™am expecting.
I am going to check if I can find an open template with a game, where scores for each user are calculated and there should also be a ranking board.
Not sure if I am right, but â€śsomething say meâ€ť, each streak should get an independ indentifier, to show that they are belonging to one and the same group and then I think it will become more easy to rank them by this indentifier
I added the logic @Jean_Pierre_Traets.
Two parts of the solution:
Figure out which rows are the end of a streak (so that when youâ€™re sorting by cumulative days, youâ€™re excluding the days that are just ongoing streaks). In my formula itâ€™s a day where the next date has 0 as cumulative score, or doesnâ€™t exist yet (to count in the ongoing streak thatâ€™s happening right now)
Then itâ€™s easy to filter, sort, and take the .Nth()
item
P.S.
This is a great statement that allows me to share one more piece of problem solving knowledge.
Which is, ask yourself, "Do I actually need to calculate all this?"
And if not, "Okay, what is the simplest thing I actually need to calculate here?"
Letâ€™s say you decided to assign each streak an identifier. And thatâ€™s something that you may have actually wanted to do, e.g. if you wanted to color-code each streak group or something. Youâ€™d probably have to introduce a separate table of Streaks with date bounds, or write a very convoluted formula. You could then probably calculate the Max()
from each group and there have your numbers. That would be a computationally complex solution.
But in your case, only the final numbers per streak matter. So do you really care whether this or that date belongs to a certain streak? No, you only care that you know the â€śfinalâ€ť numbers of each streak (since theyâ€™re already Max by nature). So itâ€™s a good idea to think whether you can simply filter your subset down to only those rows/numbers. You know that when a new streak starts, it starts counting from zero. So you can write a formula that checks whether the next row is zero â€” this means this one is the end of a streak.
Another example.
Say, you want to zebra-stripe your streaks (odd streak with white background, even streak with grayish background, to tell those apart).
Do you need to assign each row some group reference?
Yes, although you donâ€™t need a reference but simply a number (e.g. group 1, 2, 3, 4â€¦)
How can you calculate that? Do you still need to create a Streaks table and then calculate an index of each Streak, and then look it up?
You could do thatâ€¦ but also you could just calculate how many â€śis end of streakâ€ť checkboxes are there before this current row
thisTable.Filter(
[Is end of streak]
AND CurrentValue.Date < thisRow.Date
).Count()
This approach is a staple to any algorithm programming. Learning to think of the simplest operations needed is key to writing effective calculations (and also passing coding interviews). Of course you still need to know how each small operation behaves and what implicit computational complexity it holds.
Perhaps, actually making a Streaks
table would be a more efficient solution, computationally speaking. It would come with its own downside though: the need to maintain that table and add new rows whenever a new streak is happening.
Read / watch some videos on asymptotic complexity to get what I mean.
Dear @Paul_Danyliuk,
Your solutionâ€™s simplicity, logic thinking and identifying steps towards the solution are amazing like always Paul
Just one small thing, the first day (0) should be added to get the right amount of days in the streak.
Thank you / Đ±Đ»Đ°ĐłĐľĐ´Đ°Ń€ŃŹ