1 Reply Latest reply: Jul 24, 2017 3:45 AM by Mangal Kamble

# Maximum value from moving average

Hi,

I'm stuck with this issue for a couple of days now and I think I need some help.

I have a chart which shows, for each minute, how many items we detect (total items per minute.xls) within a selected range of time. My formula for this one is:

if(DateTimeMinMaster >= vi_HourMinuteMin and DateTimeMinMaster <= vi_HourMinuteMax,count({<DateTimeMinMaster = {">=\$(=vi_HourMinuteMin)<=\$(=vi_HourMinuteMax)"},Station={'7000'}>}  distinct Code))

And my dimension is DateTimeMinMaster.

In other chart I have the flow of items per hour on a moving average of 15 minutes (item flow per hour.xls), which formula is:

if(DateTimeMinMaster >= vi_HourMinuteMin and DateTimeMinMaster <= vi_HourMinuteMax,RangeAvg(Above(count({<DateTimeMinMaster = {">=\$(=vi_HourMinuteMin )<=\$(=vi_HourMinuteMax)"},Station={'7000'}>}  distinct Code)*60,0,15)))

And the same dimension DateTimeMinMaster.

If we look at the excel item flow per hour.xls file we can see that our maximum value is 40 at 16:46, but if I try to show in a text box this max value with the formula:

=Max(

Aggr(if(DateTimeMinMaster >= vi_HourMinuteMin and DateTimeMinMaster <= vi_HourMinuteMax,

RangeAvg(Above(count({<DateTimeMinMaster = {">=\$(=vi_HourMinuteMin - 0.0104166)<=\$(=vi_HourMinuteMax)"},Station={'7000'}>}  distinct Code)*60,0,15)))

,DateTimeMinMaster )

)

what I get is 28.

My guess is that when I use the Aggr it excludes somehow the zero/null values from the calculation of the moving average and results in a complete different set of values.

How can I get the maximum value I see in the chart isolated in a text box?