Set Analysis

[thannila]

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

[Anil_Babu_Samineni]

Output please and which object?

[vishsaggi]

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?

[qlikviewwizard]

Hi Try this,

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

7

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

2

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

2

[Anil_Babu_Samineni]

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.

[qlikviewwizard]

Hi thannila

Did you get the answer?

[thannila · Accepted solution]

Yeah I got the answer.

This is the solution

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