Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
Thank you. It worked perfectly