Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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,
Hi
it would be easy if any sample data is shared. Needn't be actual data.
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
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,
in table, you can use a measure as;
=aggr(sum(distinct BedDays)AccountID)
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.
@eddie_wagt thanks,
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,
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?
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
@Ciara I am glad it worked, little by little you are filling your tricks bag !!!!