Average count by day of month

[#1]

I am trying to create a graph that will show the average people visiting per day of the month.  My data has number of visits per hour in a record (ie i have 8 records for day - one for each our of the day).  When multiple months are selected, I can not get my graph to work correctly.  I have an expression that calculates the average visit using avg(visits) with a dimension of day(shopping day).  This gives me the average visits per hour because my data gives visits per hour.  I can change the expression to sum(visits) and it gives me the total visits for all day 1's all day 2's like you would expect.  Is there a way to get the expression to calculate an average per day even though my data records have visits per hour?  I tried to change th expression to sum(visits)/count (distinct shopping day) and it wouldnt display anything.

Any ideas??

[#2]

Hi ,

Could you please share your data and sample output

BR,

Anitha

[swuehl · Accepted solution]

I would think that your idea of summing the visits per day of month across all selected month and then dividing by the number of distinct dates (shopping day is a date, right?) is ok and should return a value.

(By the way: If you use field names like shopping day (including e.g. spaces), I think you have to enclose them with square brackets, like [shopping day], I assume you just missed that in your post?)

If your shopping day is a timestamp, you will probably need to use a count(distinct daystart([shopping day])).

I would also create the day / month / year / daystart fields all in the script.

If you still have problems, maybe you could either upload a small sample app here or describe your data model and dimensions and expressions used in the chart a bit closer.

Hope this helps,

Stefan

[#4]

This seemed to be the key:   If your shopping day is a timestamp, you will probably need to use a count(distinct daystart([shopping day])).Thanks