Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Using Distinct in expression for bar chart

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

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Contributor III
Contributor III

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

Highlighted
Partner
Partner

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

View solution in original post

Highlighted
MVP & Luminary
MVP & Luminary

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.

Oleg Troyansky

Masters Summit for Qlik is going virtual! Dec 16th - I'll teach Performance Tuning

Highlighted
Contributor III
Contributor III

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