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

[kavieweg]

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

[wdchristensen]

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

[kavieweg]

Hi William,

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

Mathias

[wdchristensen]

My first thought was that you could use the Now(1) function.

https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/DateAndTimeFunction...

0 (time at last finished data load)
1 (time at function call)
2 (time when the app was opened)

From my initial test app, it did not work as I expected. It looks like Qlik is caching the information and not reloading as I expected. I will attached the example app so that others can help tweak it.

[wdchristensen]

Please see the attached example app.

[dwforest]

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

OrderWt + Peek(TotalWt),0) AS TotalWt

[kavieweg]

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

[dwforest]

My solution is to be added to your LOAD script.

If you need to reset every day,

LOAD

     (your fields)

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

FROM

your table;

[wdchristensen]

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!

[wdchristensen]

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

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