13 Replies Latest reply: Jul 8, 2015 3:52 AM by Monika Piątkowska

# Variables with sum and filters

I have such variable (in Variable Overview): vCost = SUM([Cost1])+SUM([Cost2]).

I want do sth like this (in Chart Properties -> Expressions -> Definition): SUM( {<[Some dimension]={'Option1'}>} vCost) - but I see that it is not correct. So my question is how to get filtered variable?

Monika

• ###### Re: Variables with sum and filters

Hi Monica,

You have to add [Cost1] + [Cost2] in the variable, so without the SUM().

Greets, Halmar

• ###### Re: Variables with sum and filters

Hi Monika, you can change to vCost variable to:

Alt(Cost1,0)+Alt(Cost2,0)

And then you can use those expressions:

SUM({<[Some dimension]={'Option1'}>} \$(vCost))

• ###### Re: Variables with sum and filters

Thanks for reply. Your answer is the best as for me - you've also showed me that I can use Alt function to avoid errors (I think that this is for that ;p).

• ###### Re: Variables with sum and filters

Hi Monika, Alt() gives an alternative value when the first parameter is null.

If one record has a value in Cost1 (let's say '10') but Cost2 is null, trying to do Cost1+Cost2 it does 10+null()=null(). Using Alt() the value of Cost2 is 0, so it does 10+0=10.

• ###### Re: Variables with sum and filters

Now I have such another problem. I have vCostAll:

SUM({<[Some dimension]=>} Cost) * SUM( CostA ) / SUM( CostB )

And then I want use another filter on whole vCostAll, like this:

vFilteredCost: SUM({<[Some dimension 2]={'SomeOption'}>} \$(vCostAll))

But it didn't works. Is there any way to overcome this except writing it all in one variable?

• ###### Re: Variables with sum and filters

I don't see the same way for this, you need first each Sum() separatedly and then divide the result, so each Sum() has to have his set analysis.

Maybe using variable parameters:

vCostAll: SUM({<\$1>} CostA ) / SUM({<\$1>} CostB )

Expression:\$(vCostAll(Dimension={'A'}))

But in there the comma separates parameters, if there will be more than one field in set analisys it can get complicated.

vCostAll: SUM({<\$1,\$2>} CostA ) / SUM({<\$1,\$2>} CostB )

Expression:\$(vCostAll(Dimension1={'A'}, Dimension2={'B'}))

If any paremeter doesn't have value it still works, but if some field in set analysis needs more than one value I don't know how to do it in a simple expression, in example:

\$(vCostAll(Dimension1={'A'}, Dimension2={'B', 'C'})) // The comma in Dimension2 values will be treated as variable parameter separator.

• ###### Re: Variables with sum and filters

Hi

Try like this

In variable

SUM( {<[Some dimension]={'Option1'}>} [Cost1])+SUM( {<[Some dimension]={'Option1'}>} [Cost2])

And use vCost as expression.

Or

Directly use as

SUM( {<[Some dimension]={'Option1'}>} [Cost1])+SUM( {<[Some dimension]={'Option1'}>} [Cost2]) in expression

• ###### Re: Variables with sum and filters

Thanks for answer but it's too much writing as for me.

• ###### Re: Variables with sum and filters

Hi ,

Its coming from same table?

If so,

You can write like this'

SUM( {<[Some dimension]={'Option1'}>} [Cost1] + [Cost2])

• ###### Re: Variables with sum and filters

Nice to know, that if costs are from one table I can write like this. But the point is that I want to use sum of costs in many places, so I don't want to write their sum all the time.

• ###### Re: Variables with sum and filters

Hi Monica ,

dont declare variable with '=' i mean just write (SUM([Cost1])+SUM([Cost2])) like this in variable Overview(That means it will consider it as text)

& in Expression Change to vCost to \$(vCost) u will get the correct ans

• ###### Re: Variables with sum and filters

Yes, I know. I used '=' just to show how variable vCost looks like.