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

[Anonymous]

Hi,

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

My data looks like this:

Transaction_Year	Donor_ID
2016	D1
2017	D1
2018	D2

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?

[sunny_talwar]

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

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)))