Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

massimofavaro
New Contributor III

Help with a graph (average number of interaction of users who made atleast an interaction)

Hello everyone,

I am trying to create a column chart, but it's a little complicated for me.

Let me explain my data:

I have a table with:
request_date : the date of the interaction

id : the id of the interaction

open_id : id of the user

request_date.Year : the year of request_date

What I'd like to achieve is a column chart, with the interaction year as X axis, and the average number of action performed by users that have made atleast an interaction (in that year).

COUNT(id) / COUNT(DISTINCT(open_id))

is clearly not correct since it should give me the number of actions in a year divided by the total distinct amount of open_id across all years, I guess.

What should the formula be like?

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Help with a graph (average number of interaction of users who made atleast an interaction)

Do you have entries for open_id without an request?

If not, then why doesn't your first expression give you the correct result? can you post some sample data and your requested result?

6 Replies
MVP
MVP

Re: Help with a graph (average number of interaction of users who made atleast an interaction)

If you want the total distinct amount of open_id across all years, why not just use the TOTAL qualifier?

=Count(id) / Count(TOTAL DISTINCT open_id)

karthikoffi27se
Contributor III

Re: Help with a graph (average number of interaction of users who made atleast an interaction)

Hi Massimo,

You create a year field in the script like below

YEAR(request_date) AS REQUEST_YEAR

and then in the chart use the below expression

Count({<REQUEST_YEAR = {"$=(max(REQUEST_YEAR ))"}>}distinct id)/COUNT(DISTINCT(open_id))

This should give number of actions in a year.

massimofavaro
New Contributor III

Re: Help with a graph (average number of interaction of users who made atleast an interaction)

Sorry, I have not been clear enough, I didn't mean all users across all years, but only the ones who have made atleast an interaction in the period selected (the bar chart of 2015 must show the average of interactions of users who have made atleast an interaction in 2015 and so on)

MVP
MVP

Re: Help with a graph (average number of interaction of users who made atleast an interaction)

Do you have entries for open_id without an request?

If not, then why doesn't your first expression give you the correct result? can you post some sample data and your requested result?

massimofavaro
New Contributor III

Re: Help with a graph (average number of interaction of users who made atleast an interaction)

Wait, I guess it was really just

COUNT(id) / COUNT(DISTINCT(open_id))


It's that I didn't notice that I have very abnormal data and I thought the measure was wrong...

massimofavaro
New Contributor III

Re: Help with a graph (average number of interaction of users who made atleast an interaction)

Yep, I guess it was right all along, it's that I didn't notice I have very abnormal data and I thought the graph was wrong.