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

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