topic Re: Problem with a SUM in QlikView

Problem with a SUM

Fri, 12 Oct 2012 13:41:34 GMT

Hi everybody,

I have to do a SUM of a field but at the moment I'm in trouble.

I have two tables with these fields:

TabA:

Year, Import, account

2012, 100, G_1020

2012, 120, G_1021

2012, 155, 3089

2012, 890, G_7030

2012, 910, 4756

TabB:

Text

Included

Excluded

If the user in the designer choose field Text = excluded I don't have to sum all the fields account that starts with "G_".

So My result has to be:

155 + 910 = 1065

At the moment my expression is:

sum({ $<Year={ 2012 }> } Import)

how can implement it to resolve my problem.

Thanks in advance

Giampiero

Re: Problem with a SUM

Fri, 12 Oct 2012 13:54:47 GMT

Try this

sum({ $<Year={ 2012 }> }if(not(account like ‘G*’ and Text=’Excluded’, Import))

Hope this helps

Regards

Mhatim

Re: Problem with a SUM

Fri, 12 Oct 2012 13:55:59 GMT

One easy way is to create a flag in the script, and then use set analysis. See attached.

Re: Problem with a SUM

christophebrault — Fri, 12 Oct 2012 13:57:39 GMT

Hi,

I suggest you to add this information in your script :

TabA:

LOAD Year, Import, account,

if(left(account,2)='G_','Excluded','Included')

Re: Problem with a SUM

Fri, 12 Oct 2012 13:59:46 GMT

Hi,

I found the solution in this thread.

http://community.qlik.com/thread/56718

So my expression is:

if(GetFieldSelections(Text) = 'Excluded'

sum({<$<Year={ 2012 }, account= {"<>account like 'G*'"}>} import)

)

Thanks

Giampiero

Re: Problem with a SUM

Fri, 12 Oct 2012 14:01:23 GMT

Thank you.

It's a good idea.

I found the solution in the designer, but it'a a very good idea.

Thanks

Giampiero

Re: Problem with a SUM

Fri, 12 Oct 2012 14:05:46 GMT

Thank you.

But if (not(....)...... it doesn't seem to work.

thank you so much

Re: Problem with a SUM

Fri, 12 Oct 2012 14:07:19 GMT

Thank you.

It's a good idea too.

Thanks

Re: Problem with a SUM

Fri, 12 Oct 2012 14:20:43 GMT

not works, needed a bit of tweeking.

Use the following as is:

if(Text='Included',Sum(Import),if(Text='Excluded',Sum({$<Flag={'Non-G Account'}>} Import)))

regards

Mhatim

Re: Problem with a SUM

Fri, 12 Oct 2012 14:29:52 GMT

Thank you.

I'll try it