topic Conditional calculated column in pivot table in QlikView

Conditional calculated column in pivot table

Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

This is my first time using Qlikview and I kind of got stuck somewhere that should be relatively trivial.

I created a pivot table based on customer numbers as a dimension. Now I'm trying to create a another custom column in this table which is the sum of the sum of the amount (Amount) based on the value of another variable (X). The original table looks like this.

Customer No.	X	Amount
11111	1	10
11111	1	50
11111	1	60
11111	2	4444
11111	6	222
11111	6	333
11111	6	-100
11111	6	44
111112	1	12
111112	1	13
111112	1	25

The output table that I want should look like this:

Customer No.	Total Amount (X=1)	Total Amount (X=6)
11111	120	455
111112	50	0

What I did is:

- I created a pivot table, with customer No. as Dimension

- I created two expressions, one for each extra column. The expressions are as follows:

Sum( if(X = '1', Amount))

Sum( if(X = '6', Amount))

but I'm not getting correct results. Can someone tell me what am i doing wrong here?

Re: Conditional calculated column in pivot table

neelamsaroha157 — Tue, 25 Jul 2017 14:00:50 GMT

Try Sum({<X = {'1'}>}Amount)

Sum({<X = {'6'}>}Amount)

Re: Conditional calculated column in pivot table

sunny_talwar — Tue, 25 Jul 2017 14:00:57 GMT

How are you getting 455 for when X = 6 and Cust No. = 11111? I see 4 rows of data (222+333-100+44 = 499). If this is what you want, then try this

Dimension

Customer No.

Expressions

Sum({<X = {1}>}Amount)

Sum({<X = {6}>}Amount)

Re: Conditional calculated column in pivot table

Tue, 25 Jul 2017 14:09:24 GMT

You are right, in this case it should be 499. Sorry.

I tried your idea, I still do not get correct results. I'm afraid it has something to do with variable type? can this be the issue?

As I load the variables, I do some things with the Amount variable. Something like this:

LOAD

IF(Y = 'H',replace(Num#(Amount*-1,'0.0'),',','.'),Num#(Amount,'0.0')) AS Amount

Re: Conditional calculated column in pivot table

effinty2112 — Tue, 25 Jul 2017 14:13:10 GMT

Hi Abdullah,

You could make a pivot table with 2 dimensions Customer No. and X.

Expression: sum(Amount)

good luck

Andrew

Re: Conditional calculated column in pivot table

sunny_talwar — Tue, 25 Jul 2017 15:23:09 GMT

Can you post a screenshot for your Amount field? Because not sure what the issue is, because it worked for me with the sample provided

Re: Conditional calculated column in pivot table

Tue, 25 Jul 2017 15:37:51 GMT

here is a random screenshot from a chunk of my table.

Re: Conditional calculated column in pivot table

sunny_talwar — Tue, 25 Jul 2017 16:12:59 GMT

Looks okay to me... not sure what issue you are running into....

Re: Conditional calculated column in pivot table

Wed, 26 Jul 2017 07:06:44 GMT

is there a way to troubelshoot the problem? I'm afraid it has something to do with the column field type.

Re: Conditional calculated column in pivot table

Wed, 26 Jul 2017 07:07:10 GMT

this also didnt work .. the problem seems to be trivial but weirdly not working

Re: Conditional calculated column in pivot table

sunny_talwar — Wed, 26 Jul 2017 15:20:10 GMT

Won't know until I see it....

Re: Conditional calculated column in pivot table

gksukhi — Tue, 26 Feb 2019 21:53:44 GMT

I wanted to get individual sales for employees , when I use a similar expression as you mentioned, it gives me the total sum for all the employees....Why is it so?