Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)))