Calcuated Field using windowAvg shows "Unavailable"

Q&A
,
1

Hello:

EDIT: I’m unabled to include any links or screenshots, so ¯\_(ツ)_/¯.

I am trying to use the windowAvg function in a calculated field and, although I think I am using it correctly, it only shows Unavailable. I created a dataset (with the calculated field) and an analysis in Arena but not sure if that gets automatically linked here, so here’s the link:

Here is my function for my calculated field:

windowAvg(
    sum({unique_delivery_count}),
    [{scheduled_date} ASC],
    2,
    2,
    [{account_id}, {lookback}]
)

Here’s a screenshot of my dataset with the calculated field:

Screenshot 2025-09-11 at 12.11.29
Screenshot 2025-09-11 at 12.11.291920×554 40.7 KB

Here’s a screenshot of my analysis (with a cryptic error message):

<CAN’T ADD>

Thanks in advance,

Richard

2

In Quick Sight, the windowAvg function has some specific requirements and behaviors that might explain why you’re seeing “Unavailable” results.

Here are the most common issues with windowAvg in Quick Sight:

  1. Partition Field Limitations:

    • Quick Sight has a limit on how many distinct values can be in partition fields

    • Your partition by [{account_id}, {lookback}] might have too many unique combinations

  2. Sort Order Requirements:

    • Make sure {scheduled_date} is properly formatted as a date field

    • Quick Sight requires proper date formatting for window function sorting

  3. Data Sparsity:

    • If there aren’t enough data points within your window (2 before, 2 after) for each partition combination, Quick Sight will return “Unavailable”

  4. Calculation Limits:

    • Quick Sight has computational limits for calculated fields

Try these Quick Sight-specific solutions:

  1. Simplify your partition:

    windowAvg(
    sum({unique_delivery_count}),

undefined|----|
| [{scheduled_date} ASC], |
undefined|----|
| 2, |
undefined|----|
| 2, |
undefined|----|
| [{account_id}] |
undefined|----|
| ) |
undefined|----|
| |
undefined|----|

  1. Reduce window size initially to test:

    windowAvg(
    sum({unique_delivery_count}),

undefined|----|
| [{scheduled_date} ASC], |
undefined|----|
| 0, |
undefined|----|
| 0, |
undefined|----|
| [{account_id}] |
undefined|----|
| ) |
undefined|----|
| |
undefined|----|

  1. Check field compatibility in Quick Sight’s field list to ensure {unique_delivery_count} is recognized as a measure and {scheduled_date} as a date

  2. Verify data density - ensure you have sufficient data points across your date range for each account_id

  3. Try a different aggregation like windowSum first to isolate if it’s specific to the Avg function

Let me know if this helps.

3

Hey Murili:

Thanks so much for the quick reply. For the example that I loaded into Arena (were you able to look at that?),

  1. There are only 2 account_ids and 2 lookbacks, so that rules that out.
  2. scheduled_date is of type Date so I believe that rules that out.
  3. I ensured that the data is not sparse and intentionally used a small window, so that rules that out.
  4. I can’t imagine 4 is an issue for this simple example.

Here is my very simplified data that still causes the error:

account_id,lookback,scheduled_date,unique_delivery_count
1,6,2025-08-26,16983
1,6,2025-08-27,16393
1,6,2025-08-28,16373
1,6,2025-08-29,16363
1,6,2025-08-30,8258
1,6,2025-08-31,16362
1,6,2025-09-01,0
1,6,2025-09-02,7464
1,6,2025-09-03,0
1,6,2025-09-04,0
1,6,2025-09-05,16347
1,7,2025-08-25,0
1,7,2025-08-26,16983
1,7,2025-08-27,16393
1,7,2025-08-28,16373
1,7,2025-08-29,16363
1,7,2025-08-30,8258
1,7,2025-08-31,16362
1,7,2025-09-01,0
1,7,2025-09-02,7464
1,7,2025-09-03,0
1,7,2025-09-04,0
2,6,2025-08-26,639
2,6,2025-08-27,0
2,6,2025-08-28,0
2,6,2025-08-29,0
2,6,2025-08-30,0
2,6,2025-08-31,0
2,6,2025-09-01,0
2,6,2025-09-02,0
2,6,2025-09-03,0
2,6,2025-09-04,0
2,6,2025-09-05,0
2,7,2025-08-25,0
2,7,2025-08-26,639
2,7,2025-08-27,0
2,7,2025-08-28,0
2,7,2025-08-29,0
2,7,2025-08-30,0
2,7,2025-08-31,0
2,7,2025-09-01,0
2,7,2025-09-02,0
2,7,2025-09-03,0
2,7,2025-09-04,0

I will try using a different window function as you said, but at this point it I am at a loss.

Thanks again,

Richard

4

Hey Murili:

I double checked all the things and nothing stands out. I did make my example in Arena public and here is the link: dashboard_with_test_window_avg

I also simplified that calculated field to:

windowSum(
    sum({unique_delivery_count}),
    [{scheduled_date} ASC],
    0,
    0,
    [{account_id}]
)

And still unavailable.

Thanks again,

Richard

5

Hi @richard-seguno,

Hope everything is well! Just checking back in on this thread since there hasn’t been a reply in a while. Were you able to find a solution to your calculated field question in the meantime, or are you still running into persistent issues? If we do not hear back in the next 3 business days, I’ll go ahead and close out of this topic.

Thanks!

6

I was not able to solve this problem, and the last message I sent was never replied to. I was forced not to use this feature.

Richard

7

I’m procrastinating while waiting for a dataset to load…

This worked for me in your arena:

windowAvg(sum({unique_delivery_count}), [{scheduled_date} ASC], 2, 2, [{account_id}])

I double checked the math in Excel and I think it’s working correctly….

The window functions are tricky (and annoying). I think you HAVE To have all of the fields and history in the partition and the field it orders by in the visual for it to calculate correctly, and they ALSO have to have all of the values needed to calculate it because it calculates off of the table itself, and not in the backend underneath it.

I don’t understand why it wasn’t working for you, because your code looks correct to me.

8

Hi @richard-seguno,

Just checking in to see if you saw TRube’s recent message and/or if you were able to find a workaround to your question since. If not, I’d definitely reach out to AWS Support as they may be able to assist you further on this topic.

https://docs.aws.amazon.com/awssupport/latest/user/case-management.html

9

I did see the message, but have moved on since the response was more than 2 week after my last post. Ulitimately I found solutions outside of QS.

That said, I do think this was an issue with QS as I created a very simple example that exhibited the issue within the sandbox. ¯\_(ツ)_/¯

Richard

10

Hi @richard-seguno,

Happy to hear you were able to find a workaround regardless! Will leave this topic open for a bit in case you have any other questions pertaining to it or if others are encountering similar issues with the calculated field. Thanks again for bringing this concern up to the community!