2 Replies Latest reply: Dec 13, 2011 7:22 AM by Antoine Guillemont

# Average of a count on timscale axis build from a date

Hello,

From an access database, I load Date and Code field

DateCode
06/12/2011 02:55:0650204
06/12/2011 03:01:1910015
06/12/2011 03:04:0950204
06/12/2011 03:05:1910015
06/12/2011 03:08:2150204
06/12/2011 03:09:1310015
06/12/2011 03:22:2850204
06/12/2011 03:25:3710015
06/12/2011 03:25:5850052
06/12/2011 03:25:5838104
06/12/2011 03:25:5850240
06/12/2011 03:30:3350204
06/12/2011 03:39:4720205

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

• ###### Re: Average of a count on timscale axis build from a date

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

• ###### Re: Average of a count on timscale axis build from a date

Works perfect thanks!!

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