Qlik Community

Ask a Question

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis being ignored in one table but not another

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

1 Solution

Accepted Solutions
Specialist
Specialist

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

View solution in original post

5 Replies
Specialist
Specialist

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

View solution in original post

Not applicable

Hi Jonathan,

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

Regards,

Nick

Specialist
Specialist

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

Not applicable

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

Specialist
Specialist

Thanks for that background.

Jonathan