# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted Not applicable

## calculating value from different rows

Hello community,

sorry, the title might not be "very" clear.

This is about calculating the net value of a gross value based on the value of two other fields.

I have debtors who have an order number with one or more position(s) inside each order. Each position has a condition type (tax or discount) with an according numeric value in another column.

Here's an example:

DebitorOrderNoPositionConditionTypeConditionValue
Debtor A1010TAX500
Debtor A1020DISC20

If the value in ConditionType TAX is > 0 and DISC exists and is > 0 then I want to calculate the net DISC value (in this case divide 20/1,19).

I have no clue how to do this since these information are stored in different rows.

Hope you have an idea.

thank you,

Thorsten

1 Solution

Accepted Solutions MVP & Luminary

## Re: calculating value from different rows

Assuming Debtior and OrderNo are your chart dimensions, you can something like this:

=if(sum({<ConditionType={'TAX'}>}ConditionValue)>0

,sum({<ConditionType={'DISC'}>}ConditionValue) / 1.19

,sum({<ConditionType={'DISC'}>}ConditionValue)

)

See attached.

Qlikview Notes: Calculating a Duration Using Two Rows

-Rob

5 Replies Not applicable

## Re: calculating value from different rows

Thorsten,

1) I would load a temp table where you will populate the valid OrderNo

(several tables if you have several conditions on different lines)

Temp1:

WHERE ConditionType= 'TAX' and ConditionalValue > 0

// if there are several conditions, each time with an AND logic

Temp2:

WHERE ConditionType= 'DISC' and ConditionalValue > 0 and exists('TemporderNo1' OrderNo)

2) I would load the final table based on the existence of the temp field populated with the tests

FinalTable:

ConditionalValue/1,19 as netvalue

FROM ...

Where exists('Temporderno2', OrderNo) and  ConditionType= 'DISC'

Fabrice Not applicable

## Re: calculating value from different rows

Hey Thorsten,

Please check out the attached file and see if it helps. I used the expression as Value/119. You can change it per your needs. This will give you an idea of how to proceed.

Hope it helps

Thanks

AJ MVP

## Re: calculating value from different rows

If you have maximum two rows for Debitor, OrderNo you can join the two rows to have 1 row with all fields for every Debitor, OrderNo

Calculation will be simplified MVP & Luminary

## Re: calculating value from different rows

Assuming Debtior and OrderNo are your chart dimensions, you can something like this:

=if(sum({<ConditionType={'TAX'}>}ConditionValue)>0

,sum({<ConditionType={'DISC'}>}ConditionValue) / 1.19

,sum({<ConditionType={'DISC'}>}ConditionValue)

)

See attached.

Qlikview Notes: Calculating a Duration Using Two Rows

-Rob 