
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
1 Problem - 2 Questions, How to do various calculations on each field of an expression
Hi QlikCommunity,
I'm working with QV every now and then for a few months now. This is my first entry in this board, so: Hi to everyone!
I figured out all my problems myself, but I got stucked on this one:
I have to sum up the values from booking accounts to something like a win-loss-account
I load the booking accounts and their values from one file and
have to add the structure from another file.
I have to change a few values in the win-loss-structure (change prefixes, sum a few things up)
And here is my problem. How can I do this?
How can I recalculate the values when I have different operations to do on one expression?
Can I do it in the script?
Or do I have to do it in a pivotable?
At the moment I tried it like this in a pivotable:
if(
Win-Loss-Structure = 'Turnover', sum({<Win-Loss-Structure= {'Turnover'}>}Value)*-1, if(
Win-Loss-Structure = 'CM I', sum({<Win-Loss-Structure= {'Turnover'}>} Value)*-1 - sum({<Win-Loss-Structure= {'CM I'}>} Value), sum(Value)))
BTW it doesnt really work like that.
Its ok for Turnover,
but in CM 1 it excludes the Turnover part in the calculation... So I only get the negative Value for CM 1
I hope you get my problem and you could share some inputs how to do it in the script or pivottable.
Thank you very much!
- Tags:
- new_to_qlikview

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you post an example of what you're trying to do? An excel file with some source data and the result table will be fine.
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok, I have one file with the accounts and the values.
Booking Accounts | Value |
Sales -Internet | -213 |
Sales- Mail | -235 |
Sales- Shop | -2355 |
Costs - Internet | 67 |
Costs - Mail | 63 |
Costs - Shop | 876 |
Shipping - Internet | 234 |
Shipping - Mail | 577 |
Shipping - Shop | 76 |
Then I add the Accounting structure
Booking Accounts | Accounting-Structure - Level 1 | Accounting-Structure - Level 2 | Accounting-Structure - Level 3 |
Sales -Internet | a | A | 1 |
Sales- Mail | a | A | 2 |
Sales- Shop | b | A | 3 |
Costs - Internet | a | B | 1 |
Costs - Mail | a | B | 2 |
Costs - Shop | b | B | 3 |
Shipping - Internet | a | C | 1 |
Shipping - Mail | a | C | 2 |
Shipping - Shop | b | C | 3 |
As you can see the prefix in the sales field is wrong so I have to change that and sum them up afterwards to get the values i.e. for the sales-channels.
Something like:
Internet: 2132
Mail: 657
Shop:678
I would like to do it in the load script, like
load value if structure level 2= A, value*-1, value as Value2
but when I load the first table, the info about structure level 2 is not loaded then...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
As you can see the prefix in the sales field is wrong
I see neither a sales field, nor a prefix.
If you want to use Accounting-Structure - Level 2 for calculations involving Value then you need to join the tables:
Table1:
Load [Booking Account], Value
from ...somewhere...;
join(Table1)
Load [Booking Account]
, [Accounting-Structure - Level 1]
, [Accounting-Structure - Level 2]
, [Accounting-Structure - Level 3]
from ...somewhere_else...;
If all you want is to use absolute values you can use fabs(Value) as Value. That will turn all negative values into positive values.
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I ment the minus-sign in the sales-* fields
I want to change it just in these cases.
Negative values in other variables should stay.
I already joined it.
But I cant use IF when the Account-Structure info is loaded in the next step.
Error message is: "Field not found"
So I cant do it in the load script
And in the pivot my calculation doesn't work as I described in my first post.
Thanks for the help. Its not that easy when I didnt have the full qlikview-vocabulary ready, I know..

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you post an excel file with source data and a result table?
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mh, I already posted an example for that.
But maybe its better to keep it simple.
So how can I use the IF-formula when the Account-Structure info is not loaded until the next step.
(to avoid the error I mentioned above)
I would fix the negative values with that.
And then my second problem:
I want to sum it up in a pivot table.
Is it possible to design this table:
(I know its only schematic)
Turnover =sum(<Sales> Value)
CM1 =Turnover - Costs
CM2 =Turnover - Costs - Shipping
I'm usually not that bad with those things, or am I?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
im not getting your problem at all but my english isnt first level so.
Anyway if you want to transform the negative values use the function
fabs(x)
The absolute value of x. The result is a positive number.
Cheers
