Jump to content

Aggregated SUM


Sander Homan

Recommended Posts

Hi, I have an issue, which I cant resolve.

I have multiple rows and one of the columns is called Weight.

I want to add a Calculted Column which calculates the Total Weight per Month/Year.

Issue is, is that the weight is linked to a product, but the products don't have the same amount of rows in the data.

Eg.

IDProd Weight Mnd Year

1 XXX 2344 Jun 2018

2 XXX 2344 Jun 2018

3 ZZZ 3344 Jun 2018

4 ZZZ 3344 Jun 2018

5 ZZZ 3344 Jun 2018

6 ZZZ 3344 Jun 2018

 

AVERAGE([Weight]) over ([Month],[Year]) doesn't work, because the products all have their own different amount of records, which causes one product to have a bigger influence then another product. In this example the outcome of the formule is 3011(wrong) iso 2844 (correct).

Any tips

Link to comment
Share on other sites

Hi Sander

You would have to pivot the data in order to do that.

You can add another table that is a copy of the initial table and then do a Pivot transformation where you use Prod as the identifier, you will then get a average value on the products that you can then do another average of to get the value you wanted.

Link to comment
Share on other sites

Hi Sander, the problem is that if you do averages, you will always use the row count and then you cannot make an average of the average as you want, as the average will always look at the number of rows, I suggest that you add an enhancement request in our ideas portal to have a specific option for this, ideas.tibco.com.
Link to comment
Share on other sites

Hi Hans, thanks for the quick reply

 

I did a search and this link shows some kind of solution, but when I try it I don't get the correct solution.

 

https://stackoverflow.com/questions/38321514/averaging-aggregatedsum-values-in-spotfire

 

I tried the following: Sum([WEIGHT]) OVER (Intersect([PROD],[Month],[Year])) / UniqueCount([PROD])

 

Any thoughts of this would work (after some adjustments)

Link to comment
Share on other sites

Hi

assuming the average you want to see is (2344+3344)/2=2844, and at the expense of one extra column, try this:

1. calculate column [mask] as

case [iD]=First([iD]) over (Intersect([Prod],[Mnd],[Year]))

when True then 1

else 0

end

this will be 1 in only one row for each combination of Prod,Mnd,Year

2. calculate column [leanAvg] as

SUM([Weight] * [mask]) / Sum([mask])

You cannot simply average Weight*Mask as it will still count all rows, so you need to divide by the number of rows with mask=1.

Gaia

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...