Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kavieweg
New Contributor II

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.

    

Orderpacking datepacking timeorder weighttotal weight so far
A01.03.201807:0050
B01.03.201807:5095
C01.03.201808:101217
D01.03.201812:30623
E01.03.201814:00831
F01.03.201815:00940
G02.03.201806:00100
H02.03.201807:501110
I02.03.201808:306721
J02.03.201811:00988
K02.03.201814:00897

Thanks for help.

Mathias

11 Replies
wdchristensen
Contributor III

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)?

kavieweg
New Contributor II

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

wdchristensen
Contributor III

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

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
Contributor III

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

Please see the attached example app.

dwforest
Valued Contributor

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

kavieweg
New Contributor II

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

dwforest
Valued Contributor

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

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
Contributor III

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!

wdchristensen
Contributor III

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)

Community Browser