11 Replies Latest reply: Apr 11, 2018 5:18 PM by Mathias Vieweg

# calculation of a value in a column depending on the sum of older entries

Hi together,

I would like to have an Diagramm where I can see the packing date and packing time on the x Axis and the "total weight so far" on the y axis.

I do not have re results of the column "total weight so far" and have to calucalte them. The value the "total weight so far" is always the sum of the order weight where the "packing time" is less than the current packing time of the packing date.

 Order packing date packing time order weight total weight so far A 01.03.2018 07:00 5 0 B 01.03.2018 07:50 9 5 C 01.03.2018 08:10 12 17 D 01.03.2018 12:30 6 23 E 01.03.2018 14:00 8 31 F 01.03.2018 15:00 9 40 G 02.03.2018 06:00 10 0 H 02.03.2018 07:50 11 10 I 02.03.2018 08:30 67 21 J 02.03.2018 11:00 9 88 K 02.03.2018 14:00 8 97

Thanks for help.

Mathias

• ###### Re: calculation of a value in a column depending on the sum of older entries

How often does the data need to update (daily, hourly, real-time)?

• ###### Re: calculation of a value in a column depending on the sum of older entries

Hi William,

we Need to update the data in realtime. Thanks in advance,

Mathias

• ###### Re: calculation of a value in a column depending on the sum of older entries

Please see the attached example app.

• ###### Re: calculation of a value in a column depending on the sum of older entries

If you just want a running total with the previous you can add:

OrderWt + Peek(TotalWt),0) AS TotalWt

• ###### Re: calculation of a value in a column depending on the sum of older entries

Hi David and William,

Thanks for your help so far.

but I cant follow :-) you :-(

My goal is to sum the values in the yellow coloumn per day. I tried myself the rangesum function but i couldnt do it from start for each day.

Mathias

• ###### Re: calculation of a value in a column depending on the sum of older entries

If you need to reset every day,

OrderWt + if(Peek([packing date])=[packing date], Peek(TotalWt),0) AS TotalWt

FROM

• ###### Re: calculation of a value in a column depending on the sum of older entries

Hi Mathias,

I added David's recommendation to the load script as well as added it to the example table in the application. I am not sure I understand your business case so it is difficult to assist much further. Specifically, how are you deriving the weight so far (sql query)? Perhaps you could revise your data table with an actual date time and example formulas. If you are hoping to make this dynamic you will have to perform those calculation in the application or your data will need to be refreshed constantly through the load script. Also, how often does the underlying data change. For example, how often to pending order turn to completed and fall off the chart? When you had weight variable you really meant wait (as in measure of time), correct? Good luck!

• ###### Re: calculation of a value in a column depending on the sum of older entries

=Interval(Now(1)-Packing_DtTm,'DD') // Days waiting

=Interval(frac(Now(1)-Packing_DtTm+1), 'mm') // Minutes waiting (Excluding Days)

• ###### Re: calculation of a value in a column depending on the sum of older entries

Interval(Now(1)-Packing_DtTm, 'D hh:mm')

• ###### Re: calculation of a value in a column depending on the sum of older entries

Hi William, I am still checking the results and come back by end of this week. Thank you for all your help. I will mark the right answer when i checked the resuts.

Best regards from Munich