Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
hamster-samster
Contributor
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)
1 Solution

Accepted Solutions
mageste
Partner - Contributor III
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:

 

calculated_dim.png

 

Please let me know if it helps.

View solution in original post

2 Replies
mageste
Partner - Contributor III
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:

 

calculated_dim.png

 

Please let me know if it helps.

hamster-samster
Contributor
Contributor
Author

Thank you. It worked perfectly