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

    Average of a count on timscale axis build from a date



      From an access database, I load Date and Code field

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