Hello, I am having difficulty editing an expression for creating a bar chart.
I have Regions and each Region has multiple Place_ID. Each Place_ID has a distinct Amount. However, Place_ID 2 will always have the same Amount even if it shows up twice. It will always be 400 (in my example below). The goal is that I need a bar (in the bar chart) for each Region that shows the total 'Amount' for that Region. The problem is that when Qlik sees Place_ID2 twice it adds 400 + 400 to make 800. That is incorrect. I need it to use Place_ID2 only once in the calculation so the 400 will only show up once. I have an example below.
Dimension: Region
Expression: Sum([Amount],DISTINCT[Place_ID]) This is very wrong - but what is correct?
For Region 1
Place_ID | Amount
1 100
2 400
1 100
3 7000
2 400
4 65
5 900
Place_ID | Amount (4 was filtered out) What I would like to see!
1 100
2 400
3 7000
5 900
*******************************
For Region 2
Place_ID | Amount
6 400
7 900
7 900
8 45
9 400
10 55
Place_ID | Amount What I would like to see!
6 400
7 900
8 45
9 400
10 55
Should show up in bar chart with Regions on x axis, sum on y axis
Region 1 = sum is 8,400
Region 2 = sum is 1,800
Thank you
