Sum BedDays for just one row of each record

[Ciara]

I have a field that is giving me the number of days a patient has stayed in a bed.

I want to calculate the sum of all BedDays. However, I'm running into an issue in that there are multiple lines for each visit due to differing factors.

Is there an easy way to sum the BedDays of each distinct AccountID? 

[jobsonkjoseph]

Hi

it would be easy if any sample data is shared. Needn't be actual data.

[Ciara]

Hi Jobsonljoseph

Yeah I was thinking that 😂  Cant really explain it well through text

Sample below:

AccountID	AdmitDate	DischargeDate	BedDays
1	10/05/2020	11/05/2020	2
2	10/05/2020	13/05/2020	4
2	10/05/2020	13/05/2020	4
3	11/05/2020	NULL	5
3	11/05/2020	NULL	5
3	11/05/2020	NULL	5
3	11/05/2020	NULL	5
4	13/05/2020	15/05/2020	3
4	13/05/2020	15/05/2020	3

So for the above the sum of all BedDays should be 14 because I only want to count once for each AccountID.  Not sure if I can do something with set analysis or if I'll have to do something with the way I'm loading my data.

Thanks

Ciara

[ArnadoSandoval · Accepted solution]

Hi @Ciara 

You can do load this data with a LOAD DISTINCT, that will remove duplicates, just give it a try by coding:

NoConcatenate
TempBeds:
LOAD DISTINCT *
RESIDENT Beds;

 The in the UI show this table in a fresh Table control (no the straight table) That should do the trick!

Regards,

[jobsonkjoseph]

in table, you can use a measure as;

=aggr(sum(distinct BedDays)AccountID)

[eddie_wagt]

Best solution is to solve this in the load like @ArnadoSandoval recommended. Otherwise it will depend how you will use the measure. If you use sum(distinct BedDays) for example in a KPI object you will get the expected result.

[ArnadoSandoval]

@eddie_wagt  thanks,

@Ciara 

This is a classic case of "dirty data" they are very common, I can't see any value bringing the extra records into the solution memory, beside, when you load the "dirty data" records you open the door for misleading results elsewhere! If they are redundant records, just keep them out, it makes the dashboards light-weight and lean

Regards, 

[Ciara]

Hi Arnaldo (my old friend) 😄

The reason I have duplicate lines of code is because I'm pulling in orders for patients also.  A patient might have multiple orders on one Account.  

I wonder what way I should do the 'BedDays' calculation separately then so that I am only left with 1 result per row?

[Ciara]

Ahh Arnaldo I think I got it working with your suggestion.

I just did a resident load but used Distinct VisitID and it appears to be working well.

Thanks so much

Ciara

[ArnadoSandoval]

@Ciara  I am glad it worked, little by little you are filling your tricks bag !!!!