Division of two aggregated field under Scripted Field

Hi All

I have data in this format

Order Month Revenue
Order001 Jan 10
Order001 Jan 20
Order001 Jan 30
Order001 Jan 40
Order002 Jan 50
Order002 Jan 60
Order002 Jan 70
Order003 Feb 80
Order003 Feb 90
Order003 Feb 100
Order004 Feb 20
Order004 Feb 10
Order005 Feb 10

Now by using Scripted Field i want to calculate Average Value whose formula is
Average Value = (Sum of Revenue / Unique Count of Orders) for a given Month
Average Value for Jan = 280/2 = 140
Average Value for Feb=310/3= 103

How can i achieve this using scripted field?

Thanks

There really isn't any way to get this information via scripted fields, but you can do this with the "Average Bucket" metric.

If you set it up like in the screenshot it will split the bucket the data by order, sum up the revenue for each bucket, and then take an average of those sums, getting the same value you described.
image

If you combine that with a "Split rows" bucket on month, you can get a visualization showing what you asked for:

1 Like

Okay, this is a good solution. Thanks @spalger.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.