topic Re: Compare and Calculate the Values from Previous Row in QlikView

Compare and Calculate the Values from Previous Row

lawrance — Thu, 21 Sep 2017 22:57:29 GMT

Hi All,

How to compare and calculate the below scenarios.

Table 1:

Total
5000

Table 2:

Actual
100
200
300

I need to achieve the below calculation in QlikView Script.

Final Table

Remaining	Calculations
4900	Total(5000) - Actual(100)
4700	Remaining(4900) - Actual(200)
4400	Remaining() - Actual(300)

Thanks,

Lawrance A

Re: Compare and Calculate the Values from Previous Row

lawrance — Fri, 22 Sep 2017 14:40:04 GMT

Hi all,

Could someone please help me to achieve the above mentioned scenario?

Thanks,

Lawrance A

Re: Compare and Calculate the Values from Previous Row

vvira1316 — Fri, 22 Sep 2017 15:30:08 GMT

here you go

Re: Compare and Calculate the Values from Previous Row

vishsaggi — Fri, 22 Sep 2017 15:50:37 GMT

Try this?

Total:

LOAD * INLINE [

Total

5000

];

Concatenate(Total)

Actual:

LOAD *, RecNo() AS RowCnt INLINE [

Actual

100

200

300

];

LET value = NoOfRows('Total');

NoConcatenate

TotalCount:

LOAD RowCnt, Actual, IF(RowCnt < '$(value)', Peek(Total) - Actual, Total) AS Total

Resident Total;

DROP TABLE Total;

NoConcatenate

Final:

LOAD *

Resident TotalCount

WHERE Len(Trim(RowCnt)) <> 0;

Drop Table TotalCount;

Re: Compare and Calculate the Values from Previous Row

lawrance — Sat, 23 Sep 2017 02:24:33 GMT

Hi Vijay,

Could you please tell me how to convert the below expression into QlikView Script.

=If(RowNo() = 1, (TotalValue - Max({<Seq={'1'}>}ActualValue)), (TotalValue - RangeSum(Above(TOTAL RangeSum(ActualValue), 0, RowNo(TOTAL)))))

Thanks,

Lawrance A

Re: Compare and Calculate the Values from Previous Row

vvira1316 — Sat, 23 Sep 2017 02:32:13 GMT

Attached is QlikView file. Do you mean in QlikSense

Re: Compare and Calculate the Values from Previous Row

vvira1316 — Sat, 23 Sep 2017 02:33:10 GMT

Oh You want to do in script instead of in chart

Re: Compare and Calculate the Values from Previous Row

sujeetsingh — Sat, 23 Sep 2017 05:18:43 GMT

Hey Seems Vishwa has given you the code with slight play and alters you can make it more dynamic. Like trying sum of the field to calculate totals.