Required to use previous values to get the result

[Sasik]

Hi Experts,

I stuck in the middle of some calculation in QlikView 11. Could you please help me.

Below is the straight table where DateLink is the dimension and remaining all are expressions.

Here for the fields 'AllCreated' and 'AllClosed' fields we have the data as shown. And 'Open' field has only first value, so to remove all the remaining values I used below logic to create 'Open2'.

Open2   --->    if(DateLink = '10/08/20',Open, 0)

 Now I need to do the actual calculation.

Open3 --> Open2 + (AllCreated - AllClosed)

Below are values to get in Open3 field

1st record --> same as Open2 (ie., 312)

2nd record   -->  312 + (205-193) = 324 (Using the values of above record)

3rd record --> 324 + (190-188) = 326 (Using the values of above record)

4th record  --> 326 + (163-162) = 327 (Using the values of above record)

5th record --> 327 +  (127-150) = 304 (Using the values of above record)

I used the below Open3 formula which is not working correctly. If this need to work I need to pass the Open3 value which is created immediately to Open2.

Open 3 formula  -->  if(Open2<>0, Open2, above(Open2) + (above(AllCreated) - above(AllClosed)))

Please let me know how I can pass the values created in Open3 to Open2. Or if there is another way to get the required output, please let me know.

So I need to get below values in Open3:

Open3

312

324

326

327

304

Please help me.

Regards

SasiK

[chrismarlow · Accepted solution]

Hi,

Maybe try something like;

iF(RowNo()=1,Sum(Open),
   Sum(Open)
   +RangeSum(Above(AllCreated,1,RowNo()))
   -RangeSum(Above(AllClosed,1,RowNo())))

So;

Cheers,

Chris.

[Sasik]

Hi Chris,

Thanks for your response. I am using some selections on DateLink field, may be this is causing sum(AllCreated) and sum(AllClosed) expressions are giving zeroes. And one more thing AllCreated and and AllClosed fields coming out of some other count expressions. I am not sure what could be the issue. Could you please help.

Regards

Sasik

[Sasik]

Hi Chris,

I just tweaked my existing expressions then your solution worked like a charm.

Thank you so much.

Regards

SasiK