Calculation of the rolling maximum of the last 24 values

[till_bentz]

Hello,

I have a question regarding the aggregation of values in a table. The scenario is as follows.

I have several info points and for each info point I have 24 values (on per hour). Now I need to calculate the rolling max of the last 24h for each info point. So far I did this using =RangeMax(Above(sum(data),0,24)). This works as long as I have no filter on the Date. If I filter out the date selection using set analysis, I have two problems:

1. I get heaps of 0.0 values in my table

2. The calculation gets really slow.

The second requirement is, that I need to calculate the monthly minimum of the above maximums. So far I have no Idea how to solve this...

My data looks kind of like this (in this case with a max of the last 3 values, and r_max and d_min would be the expected values):

+-------------------+----+------+-------+--------+

| TimeStamp         | IP | data | r_max | d_min  |

+-------------------+----+------+-------+--------+

| 01.10.13 01:00:00 | 1  | 5    |  5    |   5    |

| 01.10.13 02:00:00 | 1  | 6    |  6    |        |

| 01.10.13 03:00:00 | 1  | 7    |  7    |        |

| 01.10.13 04:00:00 | 1  | 6    |  7    |        |

| 02.10.13 01:00:00 | 1  | 5    |  7    |   6    |

| 02.10.13 02:00:00 | 1  | 6    |  6    |        |

| 02.10.13 03:00:00 | 1  | 7    |  7    |        |

| 02.10.13 04:00:00 | 1  | 8    |  8    |        |

| 01.10.13 01:00:00 | 2  | 5    |  5    |   5    |

| 01.10.13 02:00:00 | 2  | 4    |  5    |        |

| 01.10.13 03:00:00 | 2  | 8    |  8    |        |

| 01.10.13 04:00:00 | 2  | 5    |  8    |        |

+-------------------+----+------+-------+--------+

Any help would be appreciated!

Thanks in advance,

Till

[marcus_sommer]

I believe you need the aggr() function - read in help and try something like this:

max(total <Date> aggr(sum(data), TimeStamp))

- Marcus

[till_bentz]

Hi Marcus,

thanks for your reply. Unfortunately it didn't really help because I need to do the calculation in script due to the amount of data I have....

Thanks anyway

[marcus_sommer]

It is always a good idea to solve tasks or at least to prepare data within the script. I suggest you split your timestamp into date and time - it reduced the needed space/ram a lot - and create aggr-load:

t1:

Load

     *,

     floor(timestamp) as date,

     frac(timestamp) as time

From YourSource;

t2:

Load

     IP, // or any other Key, perhaps combined as IP & '|' & date

     date,

     max(data) as Maxdata

Resident t1 Group by IP, date;

- Marcus

[telepuzik]

Please find the attached if it matches your questoin.

Cyrus

[till_bentz]

Hello again,

I think this is not yet what I was looking for. I have a new Excel example....

Col h is the actual time stamp, in column "1" and "2" is the actual data, columns "max(24h) 1" and "max(24h) 2" show the data i want to calculate in the load script. column "h-23" shows the time stamp for the first value that would fall into the time span for the respective time stamp in column "h".

Maybe this clarifies my question a little bit....

[marcus_sommer]

Such a listing could be done easily per peek() in a sorted load - see the attachment.

- Marcus

[till_bentz]

Hello Marcus,

thanks for the hint, but unfortunately the result is wrong when the date changes.... see attachment.

[marcus_sommer · Accepted solution]

Hi Till,

I'm not sure which is the result what you expected - the formulas in the xls-example aren't consistent. If you want such result it could be solved per rangemax() - see attachment.

- Marcus

[till_bentz]

Thank you very much, with this addition I was able to solve my problem... I think