Count If Sum Analysis

hamster-samster — Sun, 16 Jul 2023 11:07:41 GMT

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.

Re: Count If Sum Analysis

mageste — Mon, 17 Jul 2023 18:09:58 GMT

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.

Re: Count If Sum Analysis

hamster-samster — Wed, 19 Jul 2023 06:21:15 GMT

Thank you. It worked perfectly

topic Count If Sum Analysis in App Development

Count If Sum Analysis

Re: Count If Sum Analysis

Re: Count If Sum Analysis