Count If Sum Analysis

[hamster-samster]

I have a data table that looks something like this

UserID	status	show	year	month	type
1	Registered	A	2023	Jan	Movie
2	Registered	A	2023	Jan	Movie
3	Registered	A	2023	Jan	Movie
3	Registered	B	2023	Feb	Live
3	Attended	B	2023	Feb	Live
3	Registered	C	2023	Jan	Performance
3	Registered	D	2023	Feb	Screening

I have created filter panes for status, year, and month

I need to create a chart that displays the summation on unique IDs who registered or attended the shows in 2023, and by each month

So for this data,

[A]
I want to see the result like
2023 - Jan -> 
Users who registered for 1 show: 2
Users who registered for 2 shows: 1

2023 - Feb ->
Users who registered for 1 show: 0
Users who attended 1 show: 1
Users who registered for 2 shows: 1

I have tried this expression in the measure, but it is returning me the total value
Count((Aggr(If(Sum(status) =1, 'registered','attended'),ID)))

Is there an expression I can use to get the data I want above in [A]?

Also is there an expression to tell if the user is new in year after a grouping by expression? Like if the unique user exists only in this month and this year, flag out the userid as a new user?

I am new to qlik and I have not been able to find comprehensive tutorials on the functions and expression usage of qlik.

[mageste · Accepted solution]

Hi there!

I was able to reach the results you wanted by creating a calculated dimension with the following expression:

Users Registered per Show =
aggr(
      Count({<status={'Registered'}>} UserID)
,UserID,month)

Then I  could create a table like this, using this new dimension and a Count (DISTINCT UserID) as a measure:

Please let me know if it helps.

[hamster-samster]

Thank you. It worked perfectly