topic problem using set analysis with IF statement in QlikView

problem using set analysis with IF statement

Tue, 05 Jul 2011 18:35:41 GMT

Hi community!

I have an IF statement (that works) as follows:

=IF(Sum (Schedule) >0), Sum(Schedule) - Sum(Amount), 0)

when I introduce set analysis into this IF statement, it doesn't work anymore.

for example:

=IF(Sum ( {$<SiteName = {Rochester} Schedule) >0), Sum( {$<SiteName = {Rochester} Schedule) - Sum( {$<SiteName = {Rochester} Amount), 0)

One thing to note is that the SiteName is from a Load Inline statement like below. I don't know if this is causing any of my issues however.

SiteLoc:

LOAD * INLINE [
    SiteName, Site, BrandType
    Rochester, 4005, MHL
    Rochester, 4045, MHL
    Rochester, 4043, MHL
    Rochester, 4305, USB
    Rochester, 4345, USB
    Rochester, 4343, USB
    Wisconsin, 4007, MHL
    Wisconsin, 4055, MHL
    Wisconsin, 4307, USB
    Wisconsin, 4355, USB
];

Does anybody know why i'm having these issues?

Re: problem using set analysis with IF statement

Tue, 05 Jul 2011 19:10:07 GMT

Hi! Not sure that it's the whole solution but there are syntax errors. Try:

=IF(Sum ( {$<SiteName = {Rochester}>} Schedule) >0), Sum( {$<SiteName = {Rochester}>} Schedule) - Sum( {$<SiteName = {Rochester}>} Amount), 0)

Re: problem using set analysis with IF statement

Tue, 05 Jul 2011 19:50:58 GMT

Hi Thomas,

Sorry about my bad syntax, but the syntax is not the issue. Your solution/syntax is correct, but it just won't work in my document for some reason.

Re: problem using set analysis with IF statement

erichshiino — Wed, 06 Jul 2011 12:57:04 GMT

Can you also post a load inline with Schedule, amount and the key to the sitename table?

problem using set analysis with IF statement

brenner_martina — Wed, 06 Jul 2011 13:02:54 GMT

Hi,

the expression is still wrong:

=IF(Sum ( {$<SiteName = {Rochester}>} Schedule) >0, Sum( {$<SiteName = {Rochester}>} Schedule) - Sum( {$<SiteName = {Rochester}>} Amount), 0)

problem using set analysis with IF statement

Wed, 06 Jul 2011 13:07:43 GMT

Hi,

Try this:

=IF(Sum ({$<SiteName = {"Rochester"} >} Schedule)>0,

Sum({$<SiteName = {"Rochester"} >} Schedule) - Sum( {$ <SiteName= {"Rochester"} >} Amount),

If above will not work, it must be something with your data.

Regards,

Janusz