Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Try Sum({<X = {'1'}>}Amount)
Sum({<X = {'6'}>}Amount)
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)
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
..
..
..
Hi Abdullah,
You could make a pivot table with 2 dimensions Customer No. and X.
Expression: sum(Amount)
good luck
Andrew
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
here is a random screenshot from a chunk of my table.
Looks okay to me... not sure what issue you are running into....
is there a way to troubelshoot the problem? I'm afraid it has something to do with the column field type.
this also didnt work .. the problem seems to be trivial but weirdly not working