Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Debitor | OrderNo | Position | ConditionType | ConditionValue |
---|---|---|---|---|
Debtor A | 1 | 010 | TAX | 500 |
Debtor A | 1 | 020 | DISC | 20 |
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
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
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
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
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
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
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.