topic Re: Variables with sum and filters in QlikView

Variables with sum and filters

Mon, 29 Jun 2015 11:41:17 GMT

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

Re: Variables with sum and filters

qlikhalmar — Mon, 29 Jun 2015 11:46:19 GMT

Hi Monica,

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

Greets, Halmar

Re: Variables with sum and filters

rubenmarin — Mon, 29 Jun 2015 11:50:39 GMT

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

MayilVahanan — Mon, 29 Jun 2015 11:52:19 GMT

Try like this

In variable

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

And use vCost as expression.

Directly use as

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

Re: Variables with sum and filters

Mon, 29 Jun 2015 11:54:08 GMT

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

Re: Variables with sum and filters

MayilVahanan — Mon, 29 Jun 2015 12:08:27 GMT

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

Mon, 29 Jun 2015 12:42:43 GMT

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

Tue, 30 Jun 2015 07:33:11 GMT

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

Re: Variables with sum and filters

Tue, 30 Jun 2015 07:35:37 GMT

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

Tue, 30 Jun 2015 07:42:16 GMT

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

rubenmarin — Tue, 30 Jun 2015 10:01:41 GMT

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

Fri, 03 Jul 2015 06:45:53 GMT

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

rubenmarin — Mon, 06 Jul 2015 10:52:05 GMT

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

Wed, 08 Jul 2015 07:52:04 GMT

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