topic Re: How to sum with a variable in QlikView

How to sum with a variable

Wed, 25 Nov 2020 16:16:04 GMT

hi, so I created a variable in settings and now I want to use this variable when summing up things.

Variable: Turnover

trade_price*traded_amount

Expression:

sum(If(IsNull(sub_exec_broker),Turnover))

Re: How to sum with a variable

Fri, 19 Aug 2016 11:14:13 GMT

sum(If(IsNull(sub_exec_broker),$(Turnover)))

Re: How to sum with a variable

its_anandrjs — Fri, 19 Aug 2016 11:14:43 GMT

Try with

sum(If(IsNull(sub_exec_broker), $( Turnover ) ))

Regards

Anand

Re: How to sum with a variable

florentina_doga — Fri, 19 Aug 2016 11:17:46 GMT

try this

sum(If(IsNull(sub_exec_broker),$(Turnover)))

Re: How to sum with a variable

its_anandrjs — Fri, 19 Aug 2016 11:20:54 GMT

Update:- When create the variable Turnover do not use = sign above the fields

Ex:-

Let Turnover = 'trade_price*traded_amount';

And expression will be =sum( $(Turnover) )

Regards,

Anand

Re: How to sum with a variable

Fri, 19 Aug 2016 11:21:49 GMT

Not Working. Should I change anything to the defintiion of the variable?

Re: How to sum with a variable

Fri, 19 Aug 2016 11:26:53 GMT

Sorry doesnt give me anything

Re: How to sum with a variable

Fri, 19 Aug 2016 11:34:12 GMT

Doesnt sum i jsut get 0 as value

Re: How to sum with a variable

its_anandrjs — Fri, 19 Aug 2016 11:34:50 GMT

Create variable like

Let Turnover = 'trade_price*traded_amount';

Your Expression:-

sum( If ( IsNull( sub_exec_broker ) = -1, $(Turnover) ) )

Hope this works

Regards

Anand

Re: How to sum with a variable

Fri, 19 Aug 2016 12:11:24 GMT

I appreciate your effort, but it doesnt work I get 0 as value I have made the LET step and use your expression.

Re: How to sum with a variable

Anonymous — Fri, 19 Aug 2016 12:21:46 GMT

Hi Niklas,

Why have you created this var? Do it more simple: Sum(If(IsNull(sub_exec_broker),trade_price*traded_amount).

Other option is: Sum({<sub_exec_broker={'*'}>}trade_price*traded_amount), here you will sum all values for your product, but only when sub_exec_broker has value.

Third option is use variable functions:

Your Variable: Sum({<$1={$2}>},$3*$4)

Your Expression: $(YourVarName(sub_exec_broker,'*',trade_price,traded_amount))

It will be translated like this: Sum({<sub_exec_broker={'*'}>}trade_price*traded_amount)

Regards!

Re: How to sum with a variable

its_anandrjs — Fri, 19 Aug 2016 12:26:54 GMT

In any text object or the chart check what output you get from the below expression

IsNull( sub_exec_broker )

and then use that condition to check for Isnull() function, Also Isnull() function return -1 if the condition is true.

Regards,

Anand

Re: How to sum with a variable

Fri, 19 Aug 2016 12:38:32 GMT

Hey Manuel,

Thanks but the reason is:

sum(If(IsNull(sub_exec_broker),(trade_price*traded_amount)))

This works but I need to make further calculation.

sum(If(IsNull(sub_exec_broker),(trade_price*traded_amount/rate_eur*rates)))

should give me the final and expected value but this doesnt work. This just gives me 0 in an expression therefor I was trying to make it with a variable. If you undestand? Maybe you have another suggestion in doing this?

Re: How to sum with a variable

trdandamudi — Fri, 19 Aug 2016 12:40:58 GMT

I think the reason you are not getting the result because the field sub_exec_broker value is not a real null. So use the below expression: Hope this helps....

sum(If(Len(Trim(sub_exec_broker))=0,$(Turnover) ))

Re: How to sum with a variable

Anonymous — Fri, 19 Aug 2016 12:48:17 GMT

Hi Niklas,

Try this:

sum(

If(

LEN(Replace(Replace(sub_exec_broker,' ',''),Chr(10),''))=0, //Here you can have some chars, i deleted returns and blanks

(trade_price*traded_amount) / //be carefull with operators priority

(rate_eur*rates)

)

Regards!!!

Re: How to sum with a variable

Fri, 19 Aug 2016 13:16:02 GMT

You see the picture above. Turnover is calculated by using this expression:

sum(If(IsNull(sub_exec_broker),trade_price*traded_amount)) <- Works fine!

Then I want to jsut convert it to EUR in second column by just adding:

sum(If(IsNull(sub_exec_broker),trade_price*traded_amount/rate_eur))

And it is not working. Any suggestions?

Re: How to sum with a variable

Fri, 19 Aug 2016 13:52:30 GMT

The only reason for this issue is that the rate_euro is null where the sub_exec_broker is empty.

Your data should be like this :

exec_broker	sub_exec_broker	traded_amount	trade_price	rate_eur
A	A1	24000	10	1,2
L		5000	10
M	M1	19000	10	1,2
M	M2	18000	10	1,2
P	P1	17000	10	1,2
P		5000	10
S		6000	10

Re: How to sum with a variable

Anonymous — Mon, 22 Aug 2016 06:02:05 GMT

sum(If(IsNull(sub_exec_broker),trade_price*traded_amount/If(IsNull(rate_eur),1,rate_eur)))