4 Replies Latest reply on Mar 16, 2018 1:01 PM by Saket Dabi

# Dynamic Windowing of rows

I would like to calculate weighted average of my data how I am only showing the average at some places in the table and i would like to some how dynamically calculate the weighted total wherever the average is not null. I want to do window sum of QTY whereever avg is not null.

my data

DateAverage (calculated aggregate filed)

QTY

sum(QTY)

Weighted Avg
2/1/2018NULL100
2/2/2018NULL100
2/3/2018NULL2000
2/4/2018NULL3000
2/5/20883100(3*100)/2112
2/6/201852000(5*2000)/2112
2/7/20183.512(3.5*12)/2112
2/8/2018NULL1000
2/9/2018NULL1000
2/10/2018210002*1000/1120
2/11/201842020*4/1120
2/12/201851005*100/1120
• ###### 1. Re: Dynamic Windowing of rows

Hi Saket,

Pl check Screenshot and attached. Pl mark it Helpful and CORRECT, If it Helps.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 2. Re: Dynamic Windowing of rows

Thanks deepak, this would work but average is already an aggregation so its not allowing me to do underlined

(average*qty)/{SUM( if not isnull(average) then qty end)}

Thoughts ?

• ###### 3. Re: Dynamic Windowing of rows

I hope you can Follow. It is bit Complex but Done. I aggregated your Average b4 Calcs.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 4. Re: Dynamic Windowing of rows

my data is bit more complex , but just thought about your solution it wont work when u have a pattern of null values and average values , I have changed the question to exmaplain. Ideally i want to bucket the sum(weight) for group of places where the average is not null. your solution would group the whole column.