first occurrence

[#1]

Hi to everybody,

this is my first post as a very new QlikView user .

I tried to find a solution looking around in this forum but as my English is not perfect and I don' know very well QlikView syntax i was not able to find it.

I'm trying to create a report for a non-profit ngo organization analyzing donation and donors.

My problem is to find a way to show how many new donors i have in a period, let say a month.

I'm reading data form an sql server with an anagrafic table with all donators' personal data and a linked table with donations.

So I have FK_ANA_VER_COD as link form anagrafic data (VER_NAME, VER SURNAME, and so on) and donations: for each donation i have a date (VER_DATA).

I create a pivot table with month name as dimension and a lot of
expression like count of donations, count of donator, average value of donations and so on.

the only question I'm not able to answer is how many "first time donator" I have in that month.

Can anybody help me?

Sorry again if this question was already answered but I was not able to find it.

[#2]

The easiest way would probably be to Load a first donation date into your dataset.

In SQL, you could use:

SELECT VER_DONOR_KEY, MIN(VER_DATA) AS FIRST_DONATION_DT
FROM DONATIONS
GROUP BY VER_DONOR_KEY

That would give you a table where every donor has a first donation date. Depending on what you want to do with it, it may be better to have that as a first donation month and year. You could do the date, month, and year all as separate fields, which would make it easier to count first time donators by month.

[#3]

You may use similar SQL syntax in the Qlikview Script, in which you are loading your data. It is possible for you to group your user by the key, and find out earliest date of donation.

sorting by earliest date, you are get the new donors for each day.