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

4

Hello, just following up. I am still in need of assistance.

5

Hi @hcorbett,
Take a look at the attached Arena view below, it’s a unique work around but should work out for you. The difficult issue here is since you’re dealing with all rows containing the same ID, there’s no clear cut way to partition the values to separate and restart the count.

CalculatingDailyStreak - Analysis Copy