How do I calculated the number of days in a row the same value occurs? Daily streak?

Question & Answer
,
1

I am trying to calculate the daily streak for my data. I have different items that are assigned different statuses: Okay, Warning, and Critical.

I want to know for how many days in a row an item is in a certain status.

For example:

image
image272×302 3.04 KB

I’ve researched a bunch of things but haven’t yet found a solution.

The closest I’ve gotten is

runningCount(count({ID}), [{date} ASC], [{Status}])

but the streak count doesn’t start over when the status changes. I.e. The first instance of Critical is 1. Then the Status is Warning (Streak = 1), but then it goes back to Critical and instead of starting over at 1 because the streak was broken, the value is 2.

2

@hcorbett ,

Could you add an Arena example for the above ( QuickSight Arena -- An embedded instance of QuickSight within the QuickSight Community )

The first instance of Critical is 1. Then the Status is Warning (Streak = 1), but then it goes back to Critical and instead of starting over at 1 because the streak was broken, the value is 2.

I am not sure I completely understand the above.

3

The above is incorrect. It’s just the results of the formula that got me the closest to what I wanted.

The table is what I’m expecting to see (dates are sorted in desc ordering so read from bottom up)

Here’s an Arena link. Hopefully I did it correctly.

https://community.amazonquicksight.com#Arena-dashboardId=57a1ce58-1620-402a-8b1d-5c1b8d4992e3&authorId=CU-20096