Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Use set expression to get count of records where ID column does not appear in the year before?

Hi,

Newbie alert here. I am trying out Qlik Sense Cloud.

My data looks like this:

Transaction_YearDonor_ID
2016D1
2017D1
2018D2

What I am trying to achieve is count for each Transaction_Year, count the distinct Donor_ID which did not appear in the one year before the current Transaction Year.

What I have tried is:

1. add a column Donor_List_PreviousYear with the set expression below and it behaves as expected.

"Above(Concat({1} distinct Donor_ID, ', '))"

2. add a column Count_CurrentDonor with the set expression below and it returns the simple distinct Donor_ID count, just as expected.

Count({<Donor_ID = {$(=Concat({1} distinct Donor_ID, ','))}>} distinct Donor_ID)

3. add a column Count_CurrentDonor_DonatedLastYear with the set expression below but it does not count the Donor_IDs which existed in the year before

Count({<Donor_ID = {$(=Above(Concat({1} distinct Donor_ID, ',')))}>} distinct Donor_ID)

Can any of the gurus out there show me why this doesn't work and/or any workaround?

1 Reply
Highlighted

Is this what you are after? I did this in QlikView, but Qlik Sense Cloud should be the same...

Capture.PNG

Try this

Aggr(If(Donor_ID = Above(Donor_ID), 0, 1), Donor_ID, Transaction_Year)

or this

Aggr(If(Donor_ID = Above(Donor_ID), 0, 1), Donor_ID, (Transaction_Year, (NUMERIC)))