AnsweredAssumed Answered

Sum of Field in Found Sets using Self-Join (not Summary Field)

Question asked by impactbs on Apr 18, 2012
Latest reply on Apr 18, 2012 by philmodjunk

Title

Sum of Field in Found Sets using Self-Join (not Summary Field)

Post

Hello,

 

I'm trying to calculate three different Totals in a found set for a table that records deliveries of a multitude of products to a multitude of locations (so I need to summarize based on 3 criteria: Location ID, Product ID, Date):

1. NumberOfLocations: Currently Summary(Count of LocationID)

I can't figure out a way to have a summary field count only uniques, I think this has to be accomplished through a script/calculation field& self join but amd not sure the best approach given the other considerations below.

2. TotalQuantity: Currently Summary(Total of QuantityDelivered)

This works great as is! It is supposed to display the total quantity delivered in the found set (typically will be of one product)

3. TotalQuantityOfLocation: 

This is supposed to display the total amount delivered per location (as there is the chance that multiple deliveries will be to the same location and of the same product over time.)

I have tried self-join AND summary field but not quite getting what I'm missing. The main issue with using a Summary is that I cant summarize by location AND product ID. The self-join Sum works, however it sums across all the records, not just the found set.

 

Any help would be amazing! Thanks

Outcomes