How to correctly use Sum & Aggr together in a table

[#1]

Can someone please help me with how to correctly use Aggr/Sum fns to get this result

Attached is a simple example of how i want to display the data in a table

Basically i want to display the sum of a amount , selecting only the highest version of each trade, grouped per company

Many Thanks !

[sathishkumar_go]

HI Amit,

Plz find the attachements.

I hope this ll help for u.

-Sathish

[#3]

Hi Sathish,

Thanks for your reply .

I am still unable to get Sum of amount for highest versions per trade for all the trades

expected answer for sum of company B - 260

Your suggestion only partially solves my issue. it gets the Sum of amount for highest version for all the trades

I have tried many permutations with set analysis to get this formula correct ... but still no luck

Appreciate your help here

Thanks

Amit

[#4]

Can anyone help please ?

[fernandotoledo]

Hi,

Here´s a simple solution, based on script:

Add this code in the end of the script:

left join load distinct
 Company,
 trade_id,
 max(Version) as Version,
 '1' as MAX_FLAG
resident [Sheet1$]
group by Company, trade_id
;

This will create a flag identifing the values you want to sum and simplifing your chart´s expressions. The expression should be like:

Sum (if(MAX_FLAG=1,Amount))

Hope it helps

Best regards,

Fernando

[#6]

Thanks for your reply Fernanado,

This however does not help since i want the version to be picked up dynamically depending on the other filter criteria. Adding this calculation in the script make the selection of highest version static.

The number of version which is the highest will depend on other factors such as date selection.

I think the solution would be workable if we can get some sort of an expression which calculates the

sum of Amount for highest version of the trade for all trades

[#7]

Attached is an updated version of the problem example...

Need to use sum, aggr & group by in a clever way to solve this ...

[johnw · Accepted solution]

I don't guarantee that it's right, and there might be a simpler expression, but this appears to be working:

sum(aggr(if(Version=max(total <trade_id> Version),Amount),trade_id,Version))

[#9]

That worked perfectly !! ... Thanks John