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

To calculate the daily streak for your data in Amazon QuickSight, you'll need to use a combination of functions and possibly create a custom calculation. The runningCount function you've tried is a good start, but as you've noticed, it doesn't reset when the status changes. Here's an approach you can try:

1. First, create a calculated field to identify when the status changes:

ifelse(
    {Status} = lag({Status}, 1, {date} ASC, [{ID}]),
    0,
    1
)

Name this calculated field something like "StatusChange".

2. Then, create another calculated field to generate a group number for each continuous streak:

runningSum(
    {StatusChange},
    [{date} ASC],
    [{ID}]
)

Name this field something like "StreakGroup".

3. Finally, create the streak count calculation:

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

This final calculation should give you the desired result. It will count the number of distinct dates for each combination of ID, Status, and StreakGroup, effectively resetting the count when the status changes.

To use this in your visual:

1. Add the ID field to the rows.
2. Add the date field to the columns.
3. Add the Status field as a color.
4. Use the final streak count calculation as the value.

This approach should give you a daily streak count that resets when the status changes, matching your expected results. The streak will start at 1 for each new status and increment for each consecutive day the status remains the same.

Remember that you may need to adjust the field names to match your exact dataset structure. Also, ensure that your date field is properly formatted and recognized as a date type in QuickSight.
Sources
runningCount - Amazon QuickSight

Hello,

I understand that you want to create calculated fields for finding data streaks.

To calculate daily streaks in Amazon QuickSight, you'll need to use a combination of calculated fields that work together to track consecutive occurrences of the same status. You can make use of below functions to get desired result. runningCount - https://docs.aws.amazon.com/quicksight/latest/user/runningCount-function.html lag - https://docs.aws.amazon.com/quicksight/latest/user/lag-function.html runningSum - https://docs.aws.amazon.com/quicksight/latest/user/runningSum-function.html count - https://docs.aws.amazon.com/quicksight/latest/user/count-function.html

Ensure your data is properly sorted by date, and you'll need to handle the first row of each group separately since it won't have a previous value to compare against. Remember that QuickSight's calculation capabilities might be more limited than SQL or Python, so you may need to adjust the approach based on your specific QuickSight version and edition.

If the calculations become too complex or performance-intensive, consider pre-calculating the streaks in your data source before importing into QuickSight.

Thank you and Have a nice day!