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