topic Re: Set Analysis being ignored in one table but not another in QlikView

Set Analysis being ignored in one table but not another

Wed, 06 Feb 2013 11:32:59 GMT

Hi Guys,

I'm having an issue with Set Analysis on a chart where it doesn't appear to be applying the field specified.

The expression is built around 2 set analysis expressions being added;

count({$<[Financial Year] = {'$(vMaxYear)'},RowCountInbound = {"=Skills=SkillsCheckInbound"}>}distinct CallID)

Sum({$<[Financial Year] = {'=$(vMaxYear)'},RowCountHist = {"= SkillsCheckHist = Skills"}>}[Calls Offered])

The first line works as expected, however the second line doesn't seem to applying the [Financial Year] = $(vMaxYear) part. Even when i cut the expression down to just;

Sum({$<[Financial Year] = {'=$(vMaxYear)'}

//,RowCountHist = {"= SkillsCheckHist = Skills"} // Am now excluding this line just to check the Financial Year aspect

>}[Calls Offered])

it doesn't work via set analysis. However, selecting the field from a selection box gives the desired result.

I'm sure it's to do with how i've set the tables up in the load script but i cannot see where i've gone wrong.

I enclose the file, as i expect this will be easier for someone to discern by looking than explain in text. Please be aware that i've had to join 3 tables using a key to be able to get the chart to calculate correctly so please take a look at the table layout first.

Thank you for your help in advance.

Nick

Re: Set Analysis being ignored in one table but not another

Anonymous — Wed, 06 Feb 2013 11:42:47 GMT

I think yo have one too many equals signs in the second part of the expression:

count({$<[Financial Year] = {'$(vMaxYear)'},RowCountInbound = {"=Skills=SkillsCheckInbound"}>}distinct CallID)

Sum({$<[Financial Year] = {'$(vMaxYear)'},RowCountHist = {"=Skills=SkillsCheckHist"}>}[Calls Offered])

See attached.

Jonathan

Re: Set Analysis being ignored in one table but not another

Wed, 06 Feb 2013 11:45:10 GMT

Hi Jonathan,

Sometimes it really is "Cant see the wood for the trees"! Thank you very much.

Regards,

Nick

Re: Set Analysis being ignored in one table but not another

Anonymous — Wed, 06 Feb 2013 13:39:27 GMT

I know the feeling : 0 )

By the way, what do these {"=Skills=SkillsCheckInbound"} and {"=Skills=SkillsCheckHist"} conditions do. I've not seens such a =...=.... syntax before.

Jonathan

Re: Set Analysis being ignored in one table but not another

Wed, 06 Feb 2013 14:30:35 GMT

Hi Jonathan,

It allows you to use set analysis where Field A = Field B.

In my first post above, i have the field Skills and i needed set analysis to count the Call I.D.s where the related field SkillsInbound equalled the unrelated field Skills. As they're not related, i had to create the field RowCount (to remove any double counting issues!) in each field, and then have the fields Skills and SkillsInbound to be compared for each row. Where they're the same, it counts the Distinct Call I.D.s which give me the number of calls.

Its based on a query i had resolved on this forum a few months ago so if you check out;

http://community.qlik.com/message/254536#254536

hopefully it should make more sense. The secret is to use a rowcount for each table,its integral to it working.

regards,

Nick

Re: Set Analysis being ignored in one table but not another

Anonymous — Wed, 06 Feb 2013 15:04:22 GMT

Thanks for that background.

Jonathan