Problem with a SUM

[#1]

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

[#2]

Try this

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

Hope this helps

Regards

Mhatim

[#3]

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

[christophebrault]

Hi,

I suggest you to add this information in your script :

TabA:

LOAD Year, Import, account,

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

[#5]

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

[#6]

Thank you.

It's a good idea.

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

Thanks

Giampiero

[#7]

Thank you.

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

thank you so much

[#8]

Thank you.

It's a good idea too.

Thanks

[#9]

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

[#10]

Thank you.

I'll try it