topic Re: Aggregating duplicate values in App Development

Aggregating duplicate values

dana — Wed, 10 Sep 2025 14:47:41 GMT

Edit:

The issue was posted here as well in a simplified way, and was resolved:

https://community.qlik.com/t5/App-Development/Aggregating-duplicate-values-over-periods/td-p/2530299

Hi People,

I have created a table in a script, which has calculated monthly totals from 2 facts:

Firstly, I aggregate Sick Patient Count based on the following key fields :

Disease

Department

Patient Status

Disease State

Year-Monthy

Then I add (left join) to the table the Total Patients Count by Department and Year- Month.

So, for 2 different key fields in a department and month, the total patients is the same (44).

The table in the UI should display :

Disease

Department

Sick Patient Count

Total Patients Count

Patient Status and Disease State are used as filters.

The issue is getting duplicate values in cases where there different key values for a department (88).

My questions are:

1 How do I display the same values for a department regardless of a disease?

2 The table is not suppose to include month or year, but should always show the same values for a department regardless of the periods selected.

3 How is the Total line of the table should be calculated?

Thanks in Advance!

Re: Aggregating duplicate values

Oleg_Troyansky — Tue, 09 Sep 2025 15:28:05 GMT

Hi @dana ,

Ideally, these duplication situations should be avoided by data modeling - different levels of detail should be kept in separate tables and linked with composite keys, or concatenated in a concatenated fact table.

If you have to join the two tables, then a less elegant solution would involve using the AGGR() function. If simple sum(Patients) returns duplicate values, you can avoid it by using the following:

sum(

AGGR( min(Patients), Department, YeaMonth)
)

This formula will pick one (minimal) value of Patients by Department and YearMonth, and then sum up these distinct values to the level of your chart dimensions.

Allow me to invite you to my Qlik Expert Class that I'll be teaching in Vienna, Austria on September 22-24. I will be teaching advanced data modeling, covering some of these challenges, along with advanced scripting, performance optimization, and advanced aggregation and Set Analysis techniques. You will learn the most advanced Qlik methodologies that will help you solve tough problems like this one.

Cheers,

Oleg Troyansky

Re: Aggregating duplicate values

dana — Tue, 09 Sep 2025 15:40:47 GMT

Hi Oleg,

Thanks for your suggestions.

The expression you wrote didn't resolve the issue.
I did try to use two different tables, but it didn't help either.
The composite key I used : Department and Year-Month.

Is the key I used correct?

Thanks!

Re: Aggregating duplicate values

Oleg_Troyansky — Wed, 10 Sep 2025 12:51:10 GMT

Yes, this is the correct key. It's hard for me to guess what's wrong in your app without seeing it.

Best,

Oleg

Re: Aggregating duplicate values

dana — Wed, 10 Sep 2025 14:50:23 GMT

Thank you!

I have created a simpler model, and published a new post:

https://community.qlik.com/t5/App-Development/Aggregating-duplicate-values-over-periods/td-p/2530299

The issue was resolved.