Aggregating duplicate values over periods

[dana]

Hi,

This is an updated post about an issue with dealing with duplicate values, since I couldn't edit the original one.

I have simplified my previous post..
I created 2 tables with monthly data:

FirstInfected_Monthly table has the fields Year-Month, Disease, Department,

Hospitalization_Monthly has Patients count in a department (all of them, not only the sick ones)

In the UI,  when a table includes the field Year-Month and a year is selected, the Patients count display as expected: the same number in a department, regardless of the disease:

But when the Year-Month is removed, instead of showing the total yearly number for each department (383),

the value is aggregated by Disease:

I have tried various AGGR and Total options, but nothing worked..
I attach sample data, hoping for some help..

Thanks in advance!

[Lech_Miszkiewicz]

Hi @dana 

As far as I can tell number of patients is correct. The total adds up to 383 in each case. 

We dont really see granularity of your key field used to link both tables but I assume it is likely including month and department so taking that into consideration we can see that it is possible that for some months you may not have a record with particular disease. 

If you want to have total of patients regardless of disease then your expression may need to look like:

Sum(Total <Disease> Patient)

cheers

Lech

[dana]

Hi Lech,

I did try using TOTAL, but it didn't work as expected:

Thanks...

[Lech_Miszkiewicz · Accepted solution]

Sorry, my bad. What do you get if you just do Sum(Total  Patient)? is that what you expect? Doing it with "Total" will depend on what other dimensions you want this measure to work with. in your chart object.

[dana]

Hi Lech,

Yes! you nailed it!

I had to use total by Department. Since Total Patients only will be calculated for all the departments..

Thank you for your kind help!