Qlik Community

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.

My Task:

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
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.

See also

Qlikview Notes: Calculating a Duration Using Two Rows

-Rob

http://masterssummit.com

View solution in original post

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:

LOAD OrderNo as TemporderNo1

WHERE ConditionType= 'TAX' and ConditionalValue > 0

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

Temp2:

LOAD OrderNo as TemporderNo2

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:

LOAD ....

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
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
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.

See also

Qlikview Notes: Calculating a Duration Using Two Rows

-Rob

http://masterssummit.com

View solution in original post

Not applicable

Re: calculating value from different rows

thanks a lot for the examples. I will go through them and report back to you.

UPDATE:

I've tested several approaches and I think Rob's solution is the most suitable for me right now.

Thanks a lot again.