Aggregated SUM

[Sander Homan]

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

[Stefan Hansen 2]

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.

[Sander Homan]

Thanks for the reply.

I was hoping on a solution without having to add another table.

Isn't there any formula(s) that can do this

It's fine if I have to add more then one column to get the solution.

[Stefan Hansen 2]

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.

[Sander Homan]

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)

[Gaia Paolini]

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