Variables with sum and filters

[#1]

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?

Thanks in advance!

Monika

[rubenmarin]

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.

[#3]

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?

[rubenmarin]

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.

[#5]

Thanks a lot! Your answer was very helpful form me.