Count occurrences based on dates

[hp13]

Team,

I am new to Qlik and I am struggling on functions e.g. aggr() and count() not sure how to achieve the following?

Data:

1, PERSON1, 4/1/2024

2, PERSON1, 4/3/2024

3, PERSON1, 4/10/2024

4, PERSON2, 4/1/2024

5, PERSON2, 4/2/2024

6, PERSON1, 4/11/2024

Below is the desired output:

Person1: 5 Occurrences in April

Person2: 2 Occurrences in April

Similarly for the other months......

Any help would be appreciated! Thanks in advance 🙂

[MatheusC · Accepted solution]

@hp13 

Create a monthname field in your script:

Load
MonthName(Data) as MonthYear,

Then make your expression bringing the number of people per month.

// static for Apr 2024

count({<MonthYear={'Apr 2024'}>} customers)

// or dynamic, this is an example and your date periods might be different, you may need to adjust

count({<MonthYear={"$(=Date(max(MonthYear)-1, 'MMM YYYY'))"}>} customers) -> Apr 2024

count({<MonthYear={"$(=Date(max(MonthYear), 'MMM YYYY'))"}>} customers) -> May 2024


Regarts, Matheus

[hp13]

Thank you so much @MatheusC! This helps. I really appreciate it.