topic Re: 1 Problem - 2 Questions, How to do various calculations on each field of an expression in QlikView

1 Problem - 2 Questions, How to do various calculations on each field of an expression

Tue, 05 Nov 2013 14:05:25 GMT

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!

Re: 1 Problem - 2 Questions, How to do various calculations on each field of an expression

Gysbert_Wassenaar — Tue, 05 Nov 2013 18:48:59 GMT

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.

Re: 1 Problem - 2 Questions, How to do various calculations on each field of an expression

Wed, 06 Nov 2013 09:48:16 GMT

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

Re: 1 Problem - 2 Questions, How to do various calculations on each field of an expression

Gysbert_Wassenaar — Wed, 06 Nov 2013 09:56:50 GMT

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.

Re: 1 Problem - 2 Questions, How to do various calculations on each field of an expression

Wed, 06 Nov 2013 11:03:50 GMT

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

Re: 1 Problem - 2 Questions, How to do various calculations on each field of an expression

Gysbert_Wassenaar — Wed, 06 Nov 2013 11:14:45 GMT

Can you post an excel file with source data and a result table?

Re: 1 Problem - 2 Questions, How to do various calculations on each field of an expression

Wed, 06 Nov 2013 14:44:15 GMT

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?

Re: 1 Problem - 2 Questions, How to do various calculations on each field of an expression

Wed, 06 Nov 2013 15:32:16 GMT

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