Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

Compare rows through Set Analysis.

Hi all,

I need to compare different rows in the same table.
For example, in the table [table1] I have this data:

__________________________

ID          VALUE          YEAR

__________________________

001          YES             2014 
002          YES             2014

003          NO               2014

001          NO               2015

002          NO               2015

003          NO               2015

___________________________

I need to know "How many ID are YES in the Year 2014 and become NO in 2015".

I have thought to use the Set Analysis because I'd like to avoid to create another table.

I'm new about Set Analysis, but obviously I tried to do in this way:

count(DISTINCT {1<YEAR={2014}, VALUE={'YES'}> * 1<YEAR={2015}, VALUE={'NO'}>}  ID)

I have used the operator "*" to make an intersection between the two sets, but it doesn't work.
Can someone help me to understand how I can change my expression?

Thanks

1 Solution

Accepted Solutions

Re: Compare rows through Set Analysis.

Try this:

=Count(DISTINCT {<ID = p({<YEAR = {2014}, VALUE = {YES}>})*P({<YEAR = {2015}, VALUE = {NO}>})>} ID)


Capture.PNG


UPDATE:
or this for a dynamic solution based on selection in YEAR field

=Count(DISTINCT {<ID = p({<YEAR = {$(=Max(YEAR) - 1)}, VALUE = {YES}>})*P({<YEAR = {$(=Max(YEAR))}, VALUE = {NO}>})>} ID)

4 Replies

Re: Compare rows through Set Analysis.

Try this:

=Count(DISTINCT {<ID = p({<YEAR = {2014}, VALUE = {YES}>})*P({<YEAR = {2015}, VALUE = {NO}>})>} ID)


Capture.PNG


UPDATE:
or this for a dynamic solution based on selection in YEAR field

=Count(DISTINCT {<ID = p({<YEAR = {$(=Max(YEAR) - 1)}, VALUE = {YES}>})*P({<YEAR = {$(=Max(YEAR))}, VALUE = {NO}>})>} ID)

Not applicable

Re: Compare rows through Set Analysis.

Thank you Sunny, now it works!
I need to study your expression because I want to understand how it works.

How can I represent the result rows in a table?
I need to verify if the result of the count is ok, so I'd like to create a table with the result rows.

I tried to create a new table, but I don't undestand where i can specify an expression that allows me to obtain my result table with the columns "ID, VALUE and YEAR".

Thank you.

Re: Compare rows through Set Analysis.

Table like this?

Capture.PNG

Not applicable

Re: Compare rows through Set Analysis.

Perfect
Thank you very much!

Community Browser