topic Re: Set Analysis in QlikView

Set Analysis

thannila — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

Please help me with the following requirement.

YEAR

EMPID

2016

GIS11

2016

GIS12

2016

GIS13

2017

GIS12

2017

GIS13

2018

GIS10

2018

GIS12

2018

GIS13

If I select year 2016, all the EMPIDs in 2016 should be displayed.

If I select year 2017, GIS11 should be displayed (because other 2 EMPIDs are common in 2016 & 2017)

If I select year 2018, GIS10 should be displayed (because other 2 EMPIDs are common in 2017 & 2018)

Note: This should be accomplished only using set analysis in a straight table.

Thank you.

Message was edited by: Than Mughi

Re: Set Analysis

Anil_Babu_Samineni — Mon, 02 Jul 2018 16:13:27 GMT

Output please and which object?

Re: Set Analysis

vishsaggi — Mon, 02 Jul 2018 20:40:32 GMT

What is your expected output for 2017 and 2018. You are saying 2016 all empid's should display, in 2017 empid's not in both 2016 and 2017, But your 2016 year has all EMPIds how can you get empid's not in 2016 and 2017 for 2017?Can you elaborate please?

Re: Set Analysis

qlikviewwizard — Tue, 03 Jul 2018 09:53:51 GMT

Hi Try this,

='For 2016,it should display all EMPID :' &COUNT({<Year={'2016'}>}EMPID)

='For 2017,display the EMPID not in both 2016 and 2017 :' &count (distinct {<EMPID = P ({<Year={'2016'}>} EMPID ) >*<EMPID = E ({<Year={'2017'}>} EMPID ) > } EMPID )

='For 2018,display the EMPID not in both 2017 and 2018 :' &count (distinct {<EMPID = P ({<Year={'2017'}>} EMPID ) >*<EMPID = E ({<Year={'2018'}>} EMPID ) > } EMPID )

Re: Set Analysis

Anil_Babu_Samineni — Wed, 04 Jul 2018 13:37:41 GMT

Looks like you already got answer - set analysis

Note - I suggest you to don'e duplicate threads instead you can reply here to get correct answer.

Re: Set Analysis

qlikviewwizard — Thu, 05 Jul 2018 05:32:49 GMT

Hi thannila

Did you get the answer?

Re: Set Analysis

thannila — Thu, 05 Jul 2018 11:38:40 GMT

Yeah I got the answer.

This is the solution

concat({<Year={$(vLastYear)}+{$(vmaxyear)},EMPID=E({<Year={$(vmaxyear)},EMPID=P({<Year={$(vLastYear)}>}EMPID)>}EMPID)>}EMPID,',')