Compare and Calculate the Values from Previous Row

[lawrance]

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

[lawrance]

Hi all,

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

Thanks,

Lawrance A

[vvira1316]

here you go

[vishsaggi · Accepted solution]

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;

[lawrance]

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

[vvira1316]

Attached is QlikView file. Do you mean in QlikSense

[vvira1316]

Oh You want to do in script instead of in chart

[sujeetsingh]

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.