How to Make a Dynamic WindowSum function with a Calculated Field?

I have a calculated field that I’ve made in my Quicksight analysis that calculates the size that the window for a windowSum should be, but I’m unable to use that field when calculating the windowSum. I get an error that says “Expression {{argumentName}} for function {{functionName}} has incorrect argument type {{incorrectArgumentType}}. Function syntax expects {{functionSignature}}”.

For reference, here is my windowSum calculation that produces the error: windowSum(sum({value}), [{date} ASC], 0, {window_size}, [{reporter}])

The window_size calculated field is working as I expected, as I’ve verified by putting it into a table in the analysis. Is there any way to work around this error to use the values in this calculated field in my windowSum calculation?

The goal is to calculate a sum for a window for all the rows within a certain date range. Specifically, I want all the rows where the date is within the preceding 7 days to be summed for each row in the dataset. However, there is not a value for every date, so that’s led me to try to use this window_size field to try to make a dynamically sized window for each row to capture the correct date range, even though the number of rows in the window may be different depending on whether or not there is a row for each date.

Hi @albartel ,

A couple questions, if I may. Can you include the window_size function? And what is the reporter field?

My first thought is that the error may not be related to the end index (aka window_size) argument, but to either the aggregate function argument or the partition argument. Do you get the same error when you don’t include the partition? Do you get an error if you hard-code the window_size?



wstevens01, thanks, I’ve tried to answer your questions. Let me know if you have any ideas. I’ve played around with it quite a bit and it seems to be an issue with the end index being an aggregated field but I could be wrong. Here is the window_size function:

ifelse({window_size_1}=0 AND {window_size_2}=0 AND {window_size_3}=0 AND {window_size_4}=0 AND {window_size_5}=0 AND {window_size_6}=0, 7,
    ifelse({window_size_1}=0 AND {window_size_2}=0 AND {window_size_3}=0 AND {window_size_4}=0 AND {window_size_5}=0 AND {window_size_6}=6, 6,
        ifelse({window_size_1}=0 AND {window_size_2}=0 AND {window_size_3}=0 AND {window_size_4}=0 AND {window_size_5}=5 AND {window_size_6}=6, 5,
            ifelse({window_size_1}=0 AND {window_size_2}=0 AND {window_size_3}=0 AND {window_size_4}=4 AND {window_size_5}=5 AND {window_size_6}=6, 4,
                ifelse({window_size_1}=0 AND {window_size_2}=0 AND {window_size_3}=3 AND {window_size_4}=4 AND {window_size_5}=5 AND {window_size_6}=6, 3,
                    ifelse({window_size_1}=0 AND {window_size_2}=2 AND {window_size_3}=3 AND {window_size_4}=4 AND {window_size_5}=5 AND {window_size_6}=6, 2, 1)

It’s not very graceful, just a big nested ifelse statement based on some other calculated fields that determine one by one if the next row down is still in the window. There’s probably a better way to accomplish this, so I’m open to suggestions, but this works until I try to calculate the window sum.

The reporter field is a string field indicating the person that reported the value. Removing the partition results in the same error.

Hard-coding the indices to 0 and 5 yields the expected results with or without the partition.

Hello @albartel and @wstevens01 !

@albartel have you found a solution or a workaround for this issue or are you still running into this?

Based on the error that you are receiving and the documentation, I think that the function expects an integer rather than a field. You could try using a parameter that passes along an integer value but I imagine it would through the same error.

Hello @albartel and @wstevens01 !

It has been some time since we have heard from you but would still like to help you find a solution. If we do not hear from you in the next 2 business days this topic will be archived.

No solution found, but you can archive it, thanks.

1 Like