Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
anat
Contributor

how to get common records in current and previous year

Team,

my source data:

Id,Name,Year

1,a,2015

2,b,2015

3,c,2015

1,a,2014

4,d,2014

3,c,2014

1,a,2013

5,d,2013

6,c,2013

my requirement is

1)when i select 2015,then it should compare with previous year(2014) and display common Id column records in 2 years.(here 1,3 are common in 2015 and 2014)

2)when i select 2014,then it should compare with previous year(2013) and display common Id Column records in 2 years.(here 1 is common in 2014 and 2015)

Regards

.......

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: how to get common records in current and previous year

See attached example.


talk is cheap, supply exceeds demand
5 Replies
MVP & Luminary
MVP & Luminary

Re: how to get common records in current and previous year

See attached example.


talk is cheap, supply exceeds demand
Arjunarao
Honored Contributor II

Re: how to get common records in current and previous year

Hi gwassenaar

It is showing right results. But the why the below formula showing incorrect, I think.

Please correct me if I am wrong. Thank you.

=sum({$<Name*=p({<Year={'$(=max(Year)-1)'}>}Name)>}1)

Capture.JPG

MVP & Luminary
MVP & Luminary

Re: how to get common records in current and previous year

That's a bug in the syntax checker of the expression editor. It doesn't understand that *= means to calculate the intersection of the default set with the set calculated with the p() function


talk is cheap, supply exceeds demand
Arjunarao
Honored Contributor II

Re: how to get common records in current and previous year

Thank you Gysbert.

anat
Contributor

Re: how to get common records in current and previous year

Thank you Gysbert.

how to implement same in like a clickable object like by clicking button or by clicking text object.