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

[Anonymous]

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?

[swuehl]

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]

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.

[Anonymous]

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)

[swuehl · Accepted solution]

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?

[Anonymous]

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...

[Anonymous]

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.