CODEWITHSUNDEEP
spotfire
Han
Han

Reputation: 449

Averaging aggregated(SUM) values in Spotfire

I'm trying to Average aggregated(SUM) values, but my expression keeps doing weighted averages over the whole data set.

Table Structure

REGION  SITE_ID  MONTH  QUANTITY
A       1        01     5
A       1        02     6
A       2        01     4
B       3        01     10
B       3        02     12

Expression

Avg(
    Sum([quantity]) over (All([region]))/
    UniqueCount([site_id]) over (All([region]))/ 
    UniqueCount([month]) over (All([region]))
) over (All([region]))

To Clarify, I want to average A and B's Monthly Qty per Site

But I keep getting total qty divided by total no of site_ids divided by months

Upvotes: 1

Views: 5851

Answers (1)

S3S
S3S

Reputation: 25112

This really depends on where you are going to use it and what the REAL data looks like. This should get you started. Insert this calculated column.

SUM([QUANTITY]) OVER (Intersect([REGION],[MONTH])) / UniqueCount([REGION]) AS [AvgOverRegionByMonth]

This could be inaccurate depending on how the rest of your data looks like. You can also accomplish this in a cross table. The expressions for the Sum and Avg on the example below are as follows:

Sum([QUANTITY]) as [Sum], Sum([QUANTITY]) / Count([REGION]) as [Average]

CrossTable

EDIT

In order to ONLY get the average over the months, use this forumla:

AVG([QUANTITY]) OVER ([MONTH]) as [AvgOverMonth]

Here is what your data will look like:

Average Over Month

Upvotes: 0

Related Questions