Aggregating duplicate values

[dana]

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!

[Oleg_Troyansky]

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

[dana]

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!

[Oleg_Troyansky]

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

Best,

Oleg

[dana · Accepted solution]

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.