Sum In Where - Formula

[former_member680965]

Hi,

I am struggling to write a formula.

I need to add the [PL Daily] together for each [Trader Name Primary] for each [COB as Date] where [Trader Alias] is not null.

So this is the original data:

And this is what I am trying to achieve:

The formula I have written so far is this:

SUM=Sum([PL Daily] In([Trader Name Primary];[MUSBI - PnL Exception].[COB as Date]))

But I am struggling to add the where clause. Remember, I only want to sum the [PL daily] for each [Trader Name Primary] for each [COB as Date] where the value in the [Trader Alias] is not null.

Thank You

[nscheaffer]

Please explain "CAREFUL HERE" more fully.

[nscheaffer]

I too struggle with calculation contexts. I regularly refer to this Removing the Confusion from Calculation Contexts blog post when in a calculation context wrestling match.

I created your data with a series of unions in free-hand SQL.

It looks to me if Trader Alias is Null you just want the SUM to be PL Daily. If Trader Alias in not Null then we need to use the SUM() function with some calculation contexts and a where clause. So here is my formula for SUM...

=If (IsNull([Trader Alias])) Then
     [PL Daily]
  Else
     Sum([PL Daily] In ([Trader Name Primary]) 
        Where (Not(IsNull([Trader Alias]))) ForAll ([Trader Code]))

[former_member680965]

Cheers Noel.

That works like a treat 🙂

Thank you for your time and effort on this, you don't realise how much I appreciate it.