Announcements
cancel
Showing results for
Did you mean:
Contributor

## Count If Sum Analysis

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.

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Partner - Contributor III

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.

2 Replies
Partner - Contributor III

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.

Contributor
Author

Thank you. It worked perfectly