How to do Window Avg and Window Standard Deviation

Question & Answer
1

Hi Community,

I’m trying to convert Tableau’s window_avg and window_stdev functions to quicksight.

Quicksight has windowAvg but it requires start index, end index which I don’t have… it should be dynamic based on the view.

stdevOver, stdevpOver requires calculation level, again not sure of this… any help would be appreciated.

Tableau Calc field:

((([Field 1] -WINDOW_AVG([Field 1])/WINDOW_STDEV([Field 1]))

  • ([Field 2 ] -WINDOW_AVG([Field 2 ])/WINDOW_STDEV(Field 2 ]))

  • ([Field 3] -WINDOW_AVG([Field 3])/WINDOW_STDEV([Field 3]))

  • ([Field 4 ] -WINDOW_AVG([Field 4])/WINDOW_STDEV([Field 4]))

)

/ 4)

2

Can you give me an example of what your are trying to accomplish? If you don’t need an index, can you do an avgOver / stdevOver instead?

In regards to the calculation level… if you are not partitioning over any values you should be able to make it whatever calculation you want. Trying taking the avg of fields in the calculation and let me know if the number is off.

3

Hi @jemin. Did Max’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

4

Hi Max & @Steph,

Sorry for the delay here, I was out so couldn’t get back earlier.

@Max I tried doing that it still gives me error saying syntax is incorrect… Note I’m using actual plus sign in formula but this community UI is changing it to dot… so just for question I’m using the word plus

As I mentioned in the question, I’m trying to do

((([Field 1] -WINDOW_AVG([Field 1])/WINDOW_STDEV([Field 1]))

(Plus “+”) ([Field 2 ] -WINDOW_AVG([Field 2 ])/WINDOW_STDEV(Field 2 ]))
(Plus “+”) ([Field 3] -WINDOW_AVG([Field 3])/WINDOW_STDEV([Field 3]))
(Plus “+”) ([Field 4 ] -WINDOW_AVG([Field 4])/WINDOW_STDEV([Field 4]))

)
/ 4)

In QS I did…

(

(

({Field 1} - windowAvg({Field 1}) / stdevOver({Field 1}))

(Plus “+”) ({Field 2} - windowAvg({Field 2}) / stdevOver({Field 2}))

(Plus “+”) ({Field 3} - windowAvg({Field 3}) / stdevOver({Field 3}))

(Plus “+”) ({Field 4} - windowAvg({Field 4}) / stdevOver({Field 4}))

)
/4)

Let me know what I’m doing incorrect here.

5

You need to aggregate the fields when you take window_Avgs as well.

You can do a firstValue to get it.

(((firstValue({field_1},[field_1 ASC],[field_1]) -WINDOW_AVG([Field 1])/WINDOW_STDEV([Field 1]))

6

it didn’t work for me, QS doesn’t have the function window_stdev and de window_avg request at least 4 arguments

7

I found a solution here. First create a calculated field for average:
avgOver(sum({field}),)
then a field for standard deviation:
stdevOver(avg({field}),)
And finally another field using both:
{field average} - {field standard deviation}
You can do another field to find the plus standard deviation