Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
@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
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
Thank you so much @MatheusC! This helps. I really appreciate it.