Hi @Talha Ardic?,
You can nest the following three functions to return a rolling distinct count:
- ROLLINGLIST()
- ARRAYUNIQUE()
- ARRAYLEN()
Here is the single formula to enter as part of your step (note that the column called "region" is my example column name):
ARRAYLEN(ARRAYUNIQUE(ROLLINGLIST(region)))The innermost function, ROLLINGLIST, returns an array of all string values in your column, given a set number of rows before and after the current row. This function does not respect unique strings, as you can see from the screenshot below:
If you look at row four, you can see that our array contains duplicate values for "southwest", because this string appears in both row three and row four.
To deduplicate the array created by ROLLINGLIST, you will nest the ROLLINGLIST function inside the ARRAYUNIQUE function. ARRAYUNIQUE retains only unique values in your list. You can see how ARRAYUNIQUE impacts the array created by the ROLLINGLIST function in the screenshot below:
You can see that row 3 and row 4 both contain the same list of values, without any duplication.
Finally, since you are looking for a distinct count, you will wrap the ARRAYUNIQUE function in the ARRAYLEN function. The ARRAYLEN function counts the number of items in your list and returns an integer. Since we deduplicated your list using the ARRAYUNIQUE function, this will output a distinct count. Here's how the final three-function formula looks when applied to my sample data:
Let me know if this helps! If it does, please mark the answer as "Best answer" so that other users know that your question has been resolved.
