Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Breezy
Creator II
Creator II

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
eduardo_sommer
Partner - Specialist
Partner - Specialist

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
marcus_sommer
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

Breezy
Creator II
Creator II
Author

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

eduardo_sommer
Partner - Specialist
Partner - Specialist

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

Oleg_Troyansky

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.

Breezy
Creator II
Creator II
Author

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