topic Using Distinct in expression for bar chart in QlikView

Using Distinct in expression for bar chart

Breezy — Wed, 19 Oct 2016 19:40:53 GMT

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

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

Re: Using Distinct in expression for bar chart

marcus_sommer — Thu, 20 Oct 2016 11:42:55 GMT

If you are really sure that your data-quality and the data-model is correct you could use:

sum(distinct [Amount])

if Amount is a single value. Is Amount an aggregation of values you could use something like this:

avg(aggr(sum([Amount]), [Place_ID]))

- Marcus

Re: Using Distinct in expression for bar chart

Breezy — Thu, 20 Oct 2016 15:31:31 GMT

Thank you but that did not work. It helped put me on the right track though.

The closest I've gotten is:

(aggr(sum([Amount]),[Region]))

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?

Thank you

Re: Using Distinct in expression for bar chart

eduardo_sommer — Sat, 22 Oct 2016 23:53:24 GMT

Hi, Marcus

I think you misplaced the functions in your suggestion. Wouldn't it be

sum(aggr(avg([Amount]),[Place_ID])) ?

I think this will work.

Eduardo

Re: Using Distinct in expression for bar chart

Oleg_Troyansky — Sun, 23 Oct 2016 15:23:33 GMT

Hi Brianna,

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.

cheers,

Oleg Troyansky

Learn about AGGR, Set Analysis, and many other advanced techniques in my book QlikView Your Business.

Re: Using Distinct in expression for bar chart

Breezy — Wed, 26 Oct 2016 14:15:32 GMT

Eduardo, that worked! And looking at your formula, I understand it now. Thank you for taking the time to assist!