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?
