topic Re: How to calculate difference of the value between two records in a table in QlikView

How to calculate difference of the value between two records in a table

Reliability-Engineer — Wed, 25 Nov 2020 16:16:04 GMT

I have several tanks with level measurment. Beside of showing the level in the tank I also want to show the amount of liquid used between two timestamps.

How can I calculate this usage?

With regards

Jan Droog

Re: How to calculate difference of the value between two records in a table

Taoufiq_Zarra — Sun, 05 Jul 2020 12:00:57 GMT

Hi,

One solution :

if(previous(Datum)=Datum,previous([LevelAcid Tank (ltr)])-[LevelAcid Tank (ltr)]) as Difference

for example for this sample :

load *,if(previous(Datum)=Datum,previous([LevelAcid Tank (ltr)])-[LevelAcid Tank (ltr)]) as Difference inLine [ Datum,tTimestamp,LevelAcid Tank (ltr) 1,1,905 1,2,881 1,3,856 1,4,829 1,5,803 1,6,777 1,7,751 1,8,724 1,9,699 1,10,686 2,1,1 2,2,10 ];

the output :

Re: How to calculate difference of the value between two records in a table

Reliability-Engineer — Sun, 05 Jul 2020 19:52:26 GMT

Taoufiq_ZARRA Thanks for your example.

Got the example working, but I need this calculation in the Expression box.

It that also possible?

Re: How to calculate difference of the value between two records in a table

Taoufiq_Zarra — Mon, 06 Jul 2020 08:56:29 GMT

One solution

Dimension :

Datum,tTimestamp,LevelAcid Tank (ltr)

Expression :

=if(above(total Datum)=Datum,rangesum(above(total sum([LevelAcid Tank (ltr)]))-sum([LevelAcid Tank (ltr)])),0)

output :

Re: How to calculate difference of the value between two records in a table

Reliability-Engineer — Mon, 06 Jul 2020 19:14:46 GMT

Got this one running.

Is it also possible when LevelAcid-Tank is a calculated value?

With regards, Jan

Re: How to calculate difference of the value between two records in a table

Taoufiq_Zarra — Mon, 06 Jul 2020 20:45:27 GMT

Yes!

as you can replace the measure calculated by Column(1) in your case.

look at this example, I replaced the measure (in your case AVG.....)with Column(1)

=if(above(total Datum)=Datum,rangesum(above(total Column(1) )-Column(1)),0)

Re: How to calculate difference of the value between two records in a table

Reliability-Engineer — Wed, 08 Jul 2020 21:42:01 GMT

YES!!

That's exactly what i needed. 😀

Thank you very much for helping me out.

Jan Droog