Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Ciara
Creator
Creator

Sum BedDays for just one row of each record

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? 

1 Solution

Accepted Solutions
ArnadoSandoval
Specialist II
Specialist II

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,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post

9 Replies
jobsonkjoseph
Creator III
Creator III

Hi

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

Ciara
Creator
Creator
Author

Hi Jobsonljoseph

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

Sample below:

AccountIDAdmitDateDischargeDateBedDays
110/05/202011/05/20202
210/05/202013/05/20204
210/05/202013/05/20204
311/05/2020NULL5
311/05/2020NULL5
311/05/2020NULL5
311/05/2020NULL5
413/05/202015/05/20203
413/05/202015/05/20203

 

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
Specialist II
Specialist II

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,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post

jobsonkjoseph
Creator III
Creator III

in table, you can use a measure as;

=aggr(sum(distinct BedDays)AccountID)

eddie_wagt
Partner
Partner

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
Specialist II
Specialist II

@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, 

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Ciara
Creator
Creator
Author

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
Creator
Creator
Author

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
Specialist II
Specialist II

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

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.