Set Analysis being ignored in one table but not another

[#1]

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

[Anonymous · Accepted solution]

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

[#3]

Hi Jonathan,

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

Regards,

Nick

[Anonymous]

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

[#5]

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

[Anonymous]

Thanks for that background.

Jonathan