Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Author

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
Author

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

maxgro
MVP
MVP

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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.