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.
Expression: Sum([Amount],DISTINCT[Place_ID]) This is very wrong - but what is correct?
For Region 1
Place_ID | Amount
Place_ID | Amount (4 was filtered out) What I would like to see!
For Region 2
Place_ID | Amount
Place_ID | Amount What I would like to see!
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
If you are really sure that your data-quality and the data-model is correct you could use:
if Amount is a single value. Is Amount an aggregation of values you could use something like this:
Thank you but that did not work. It helped put me on the right track though.
The closest I've gotten is:
The problem is that the sums per Region are still overestimated. I need each Place_ID to be counted only once when adding Amount per Region. Any additional thoughts?
the correct syntax is close to what Eduardo suggested:
sum(aggr(avg([Amount]),[Place_ID], + any other chart dimensions))
I'm additing "any other chart dimensions" to ensure that AGGR is going to serve you correctly...
The core issue, however, is still hidden somewhere in your data model. In a properly designed data model, there shouldn't be any duplication of amounts. I suggest that you revisit your data model and try to eliminate the duplication in the first place.
Learn about AGGR, Set Analysis, and many other advanced techniques in my book QlikView Your Business.
Masters Summit for Qlik is going virtual! Dec 16th - I'll teach Performance Tuning