topic Re: RangeSum in App Development

RangeSum

countwise — Tue, 03 Dec 2019 10:50:12 GMT

I am trying to calculate a cumulative figure for a field (Traffic_In)

I require the accumulation to be calculated for open hours only (Site_ActiveHours). by date(Date) and by hour(Time24).

I am using the following expression for Cumu In.

Aggr(RangeSum(Above(TOTAL Sum(Traffic_In), 0,RowNo())),Site_ActiveHours=True(),Date,Time24)

This expression seems to work ok if the store opens at 9:00.

If the store opens at 8:00 the accumulation starts from 9:00 and adds the 8:00 figure at the end.(See image above.

Any help would be appreciated

Jim Diggins

Re: RangeSum

Anil_Babu_Samineni — Tue, 03 Dec 2019 11:02:11 GMT

Perhaps this way

RangeSum(Above(TOTAL Sum(Aggr(Sum({<Site_ActiveHours={'Open'}>} Traffic_In), Date, Time24)), 0,RowNo(TOTAL)))

Re: RangeSum

countwise — Tue, 03 Dec 2019 12:00:25 GMT

Hi Anil,

Many thanks for this, it solves the issue I am having in a table and combo chart.

I have tried it i a Heat Map in this way:

If(
RangeSum(Above(TOTAL Sum(Aggr(Sum({<Site_ActiveHours={'-1'}>} Traffic_In), Date, Time24)), 0,RowNo(TOTAL)))
-
RangeSum(Above(TOTAL Sum(Aggr(Sum({<Site_ActiveHours={'-1'}>} Traffic_Out), Date, Time24)), 0,RowNo(TOTAL)))
<0,0
,
RangeSum(Above(TOTAL Sum(Aggr(Sum({<Site_ActiveHours={'-1'}>} Traffic_In), Date, Time24)), 0,RowNo(TOTAL)))
-
RangeSum(Above(TOTAL Sum(Aggr(Sum({<Site_ActiveHours={'-1'}>} Traffic_Out), Date, Time24)), 0,RowNo(TOTAL)))
)

The reason for the If is that if the returned value is negative I want it to show 0.

This is accumulating the result over the whole number of days.

I require it to show a daily accumulation and start again the next day.

The result needs to be a Heat Map with Time24 along the top and Date down the left.

Hope this makes sense

Jim