Need Help In Expression

[neha_shirsath]

Hello Community,

I need your help for below.

I'm attaching the excel,

Date	A	B	C
1	50	45	816
2	52	48	820
3	54	51	823

In this,

Column C  for Date 1st is calculate- (Value(811)+ 1st Date A value(50) - 1st date B value (45))= 816

                  for Date 2nd is- (Column C value for 1st Date(816)+2nd date A(52) - 2nd Date B Value(48))=820

                  for Date 3rd is- (Column C value for 2ndt Date(820)+3rd date A(54) - 3rd Date B Value(51))=823

and so on..

Please help how to write expression for this scenario.

I'm attaching the excel.

Thanks In Advance.

-Neha

[#2]

Hi,

Use the peek() function
LOAD .... A+B+peek('C') as C ...

Fabrice

[hic]

Small correction:

     A+B+Peek('C')

will evaluate to NULL on the first row, since Peek('C') is undefined. Use RangeSum instead:

     RangeSum(A,B,Peek('C'))

HIC

[neha_shirsath]

Thanks For you valuable reply,

But I can't do this on script level as my A & B value coming from 2 different qvd's.

This is only sample excel i have created to get an idea.

Can we do this on expression level to calculate C column?

Thanks,

Neha

[qlikpahadi07]

I think you can join/concatenate the QVD and perform the same with resident which HIC has mentioned

[#6]

You will have to put A and B into one single table (use JOIN).

Sort also this table because the peek('C') function returns the value of the preceding row (so you will prefer it returns the value of the previous date)

As Henric suggested, yes use Rangesum(), ifnot the NULL will propagate.

Fabrice

[neha_shirsath]

Hello,

Thanks to all.

HIC Post help me to find solution,

i have not join the qvd's but tried it in expression itself and it works correctly.

Below is the expression for help-

sum(Value) + rangeSum(above(sum(A),0,RowNo()))-rangeSum(above(B),0,RowNo()))

Thanks

Neha