Counting consecutive days

[ccbarbeau]

Experts,

I need to count the number of consecutive days where a certain condition had been met. My data looks like:
Date                 Result
2011/05/09      90
2011/05/08      86
2011/05/07      50
2011/05/06      30
2011/05/05      95

If the condition is >35, then the result here would be 3.

Can you help? Thanks!

CC

[blom0344]

If your data is not the result of an aggregated set:

total(case when [Result] > 35 then 1 else 0 end)

[ccbarbeau]

And I would then count the number of days where the result of the case is 1. I would need to somehow connect the case to the date, since I need a count of consective days where [Result]>35.

[JGirl]

OK.  Details are a bit light on, but I think you will need to do something like:
1. Pre-sort your data on date
2. Add an 'result above target' column that is something like If ([Result] > 35) then ('Y') else ('N')
3. Do a running count that restarts at 0 when the 'above target' column is 0.
4. Pick the maximum running count from the data set

How do you intend to aggregate this?  Will you be performing this across all time periods, or only for a week etc?  Do you need the maximum date, or just the maximum measure?

I have assumed that you will always get a record for each day (rather than only have a record if a fact existed).

Hmmmmm the trick here is going to be to get that count to restart....Let me think a bit more about this....

[blom0344]

And I would then count the number of days where the result of the case is 1. I would need to somehow connect the case to the date, since I need a count of consective days where [Result]>35.

totalizing the '1' values for each date that meets the requirement is in essence the same thing as counting dates (that meet the requirement)

[ccbarbeau]

Thanks, I will see with today's data if the count is working properly - will advise.

[ccbarbeau]

Hi all,

Did not work as expected; count is only taking 1 date.
I sorted by date and placed my If statement with ([value]<94.5) in a seperate data item. A second data item with a running count on data item 1 returns the value '1'.
It should be returning 21, the number of consecutive records where ([value]<94.5) in the source table.