Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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???
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))
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))
Works perfect thanks!!
I didn't know about Disctinct and DayStart function...