topic Re: variable in QlikView

variable

upaliwije — Thu, 26 Mar 2015 16:20:53 GMT

Hi Friends

I have created a variable and working fine when applied to a expression as follows

=($(vProfit))

but when I modify the expression like below it does not work.Will you please highlight the error in expression please

=sum({$<YEAR = {"$(=Only([YEAR]))"},T_YEAR = {"$(=Only([YEAR]))"}>} $(vProfit))

Re: variable

MK_QSL — Thu, 26 Mar 2015 16:24:40 GMT

Can you provide some sample data for this ?

Re: variable

upaliwije — Thu, 26 Mar 2015 16:30:36 GMT

It is difficult to upload the data it is big file. Can you by a look of expression say whether it is coded right

Re: variable

jpenuliar — Thu, 26 Mar 2015 16:34:15 GMT

What is the Statement for vProfit variable?

Re: variable

upaliwije — Thu, 26 Mar 2015 16:40:16 GMT

iT IS LIKE THIS

sum( {<NOTE = {'100'}>}[AMOUNT] )*-1

-sum( {<NOTE = {'101','102','103'}>}[AMOUNT] )

-sum( {<NOTE = {'105','107','108'}>}[AMOUNT] )

-sum( {<NOTE = {'130','131','132','133','134','137','138','139','140'}>}[AMOUNT] )

-sum( {<NOTE = {'120','121','123'}>}[AMOUNT] )

-sum( {<GROUP_CODE = {'MA','MB','MC','MD','ME'}>}[AMOUNT] )

+sum( {<NOTE = {'150','151','160','161'}>}[AMOUNT] )*-1

Re: variable

trdandamudi — Thu, 26 Mar 2015 16:42:25 GMT

Try the below code:

=sum({$<YEAR = {"$(=Only([YEAR]))"},T_YEAR = {"$(=Only([YEAR]))"}>} (=$(vProfit)))

Re: variable

upaliwije — Thu, 26 Mar 2015 16:50:16 GMT

That is also not working

Re: variable

jpenuliar — Thu, 26 Mar 2015 16:57:11 GMT

is it correct to assume that :

vProfit = " Sum( {<NOTE = {'100'}>}[AMOUNT] )*-1 sum( {<NOTE = {'101','102','103'}>}[AMOUNT] )

sum( {<NOTE = {'105','107','108'}>}[AMOUNT] )

-sum( {<NOTE = {'130','131','132','133','134','137','138','139','140'}>}[AMOUNT] )

-sum( {<NOTE = {'120','121','123'}>}[AMOUNT] )

-sum( {<GROUP_CODE = {'MA','MB','MC','MD','ME'}>}[AMOUNT] )

+sum( {<NOTE = {'150','151','160','161'}>}[AMOUNT] )*-1"

and you are calling vProfit in a Set Analysis..

Re: variable

upaliwije — Thu, 26 Mar 2015 17:01:23 GMT

Yes It is correct

Re: variable

luciancotea — Thu, 26 Mar 2015 17:02:58 GMT

The sum( sum() + sum()..) combination is not a valid one. You need to use aggr():

sum( aggr( sum(), ...) )

And using Set Analysis to filter data is bad practice.

Re: variable

jpenuliar — Thu, 26 Mar 2015 17:07:55 GMT

Agree with Lucian,

So if you can best describe what do you want to achieve with your Set Analysis, including which fields do you want to aggregate, and other filters you want to add in the expression.

Re: variable

upaliwije — Thu, 26 Mar 2015 17:22:15 GMT

THANKS

cNA YOU PLS HELP ME TO REWRITE THE EXPRESSION

Re: variable

luciancotea — Thu, 26 Mar 2015 17:28:12 GMT

=sum({$<YEAR = {"$(=Only([YEAR]))"},T_YEAR = {"$(=Only([YEAR]))"}>} aggr( $(vProfit), YEAR , T_YEAR ) )

and I would replace all the expressions similar to

sum( {<NOTE = {'120','121','123'}>}[AMOUNT] )

with similar

sum( if(match(NOTE, '120','121','123'), [AMOUNT]) )