Summing sums of different columns

[#1]

Hi,

I'm trying to create a calculation based on the sum of different fields. Here is what I'm after (I removed the name in the picture):

Holdings and Liabilities are defined as follows

=SUM(if(NAVID = 'Holdings', val))

=SUM(if(NAVID = 'Liabilities', val))

the NAV column is calculated like =Column(1)-Column(2)

Leverage is Column(1)/(Column(1)-Column(2))

Now, the problem is that I'm trying to create a line chart showing the leverage over time. In a chart, I cannot use the Column() function. If I try to replace the Column() with the actual expression, like:

SUM({<LeverageDate=>}if(NAVID = 'Holdings', val))
/
(
SUM({<LeverageDate=>}if(NAVID = 'Holdings', val)) - SUM({<LeverageDate=>}if(NAVID = 'Liabilities', val))
)

I get an incorrect result by a few percent. I have a feeling I need to use the aggr() function somewhere but I'm not sure how to use it.If I add a new column to the above table with the same expression, it won't work there either.

Also, I cannot even get the expression SUM({<LeverageDate=>}if(NAVID = 'Holdings', val)) - SUM({<LeverageDate=>}if(NAVID = 'Liabilities', val)) to work properly, it only returns the value of the first expression (before the minus sign).

PS: The expression is longer in reality this shows the problem in a shorter way.

Thank you in advance for any input!

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

Here's how I'd write it assuming the fields you are using in your example above

Sum({< LeverageDate=, NAVID = {'Holdings'} >} val) / Sum({< LeverageDate=, NAVID = {'Holdings', 'Liabilities'} >} val)

I'm assuming that Liabilities may be a negative number, so the aggregation of bof (the second part of the formula) will accumulate correctly (Holdings amount minus Liabilities amount).

On the other hand, the table you show doesn't have any time related dimension, so it may be the cause for some differences in the values returned.

Hope that helps

[#3]

So it's not a good idea to combine set expressions and IFs? This got me part of the way but it's still summing wrong (but in another way).I'll investigate it more.

The date is set to a single value in the table above but I'd like to get rid of this restriction in the chart with the set expression, just as you said. That part works fine.

[Miguel_Angel_Baeyens]

If possible, I always try to use set analysis. There are cases that a condition is mandatory, so I use them then. Anyway, for a dozen of thousand records, the resulting performance is better off when using set analysis instead of conditionals.

To check why it's summing wrong, change your chart to a straight table and keep dimensions and expressions as they are. Then see where the problem may be.

Hope that helps.

[#5]

It works now, I had misspelled one of the IDs. D'oh!

Thanks for your help!