Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey everyone,
I am working on a Pivot table which has to be built based on an already created table chart.
The table looks something like this:
Store Number | Store name | Account number | Account type | Amount |
1 | ABC | 1234 | L | 73 |
1 | ABC | 2536 | M | 932 |
1 | ABC | 6666 | L | 46 |
2 | PQR | 8726 | O | 15 |
2 | PQR | 1234 | L | 126 |
3 | XYZ | 2536 | M | 87 |
3 | XYZ | 9287 | P | 36 |
3 | XYZ | 6666 | L | 98 |
I do not want all the account types in the pivot. I am trying to get only account types 'L','M', and 'O' into the Pivot and calculate the % of total by diving the amount with the sum of all amount for that particular account number, and displaying only the topmost value for that account number, which would look something like this:
Store number | store name | account number | account type | amount | % of total |
2 | PQR | 1234 | L | 126 | 63.31% |
3 | XYZ | 6666 | L | 98 | 68.05% |
1 | ABC | 2356 | M | 932 | 91.46% |
2 | PQR | 8726 | O | 15 | 100% |
This would have been a very simple SQL query had I been working on that, but I am new to Qlik and this seems more complicated.
I read a couple of responses in other forums which suggest creating a variable but I am not sure how would that help me because I am kind of like grouping it by the account number.
Any help or suggestions is highly appreciated.
Thank you!
Looks like this is a duplicate post from the one below. Please continue to use the one accessible via the link below: