Average of a count on timscale axis build from a date

[#1]

Hello,

From an access database, I load Date and Code field

Date	Code
06/12/2011 02:55:06	50204
06/12/2011 03:01:19	10015
06/12/2011 03:04:09	50204
06/12/2011 03:05:19	10015
06/12/2011 03:08:21	50204
06/12/2011 03:09:13	10015
06/12/2011 03:22:28	50204
06/12/2011 03:25:37	10015
06/12/2011 03:25:58	50052
06/12/2011 03:25:58	38104
06/12/2011 03:25:58	50240
06/12/2011 03:30:33	50204
06/12/2011 03:39:47	20205

On a chart, I want to plot the number of time I get the code "10015".

I've created a time group called 'TimeScale' to do scroll up and down on the X axis as follow:

Year(Date) <--> Month(Date) <--> WeekYear(Date) <--> DayNumberOfYear(Date) <--> =Time#(Time(Date,'HH'))&'h'  (Hours of the day)

I use on the X axis two dimmension 'TimeScale' and 'Code'

I use as an expression if(Code=10015, Count(Code)) to count the number of time I get 10015 code.

First of all, this work only if I add 'Code' as a dimmension below 'TimeScale'. I am not really sur of why this is required...

Appart from that, I would like to get the count 'Hour' scale and 'Day' scale but for every other level of time scale ('Week', 'Month' and 'Year'), I would like to have an average of the number of count per day...

I have tried to do if(Code=10015, Count(Code)/Count(DayNumberOfYear(Date))) but it always returns 1...

Any idea on how to solve my problems???

[swuehl · Accepted solution]

Hi,

try

=count(if(Code<>10015,Code)) / count(Distinct daystart(Date))

as your expression, doing the if() inside the aggregation count should also solve your issue regarding the needed Code dimension.

I would suggest that your create new fields for year, month, etc. also for a real Date using daystart(Date), since your Dates are Timestamps.

Hope this helps,

Stefan

edit: from your app, I assumed you wanted to count all codes except 10015, but if it's the other way round, just use

count(if(Code=10015,Code)) / count(Distinct daystart(Date))

[#3]

Works perfect thanks!!

I didn't know about Disctinct and DayStart function...