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

    Maximum value from moving average

    Nestor Codina


      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:


      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?

      Thanks in advance.