Hi there,
I've created a report in SAP BI to calculate the length of stay of clients admitted to the hospital in any given month. I used nested IF statements and calculated the number of days for each client based on their entry and exit date. There is no issue with the formula, it gives me an accurate days count for each client. (see the chart). However, the total is inaccurate. I created a Measure and used the Sum formula to show the total hospital stay days of all clients.
Sum([LOS])
1,666
I was supposed to get 644 instead of 1,666. I'm not sure where another total comes from. Any idea why this is happening?
=If([Entry Exit Exit Date]>=[Report End Date] And [Entry Exit Entry Date]<=[Report Start Date];
(DaysBetween([Report Start Date];[Report End Date])+1);If([Entry Exit Exit Date]>=[Report End Date]And [Entry Exit Entry Date]>=[Report Start Date];(DaysBetween([Entry Exit Entry Date];[Report End Date])+1);If(IsNull([Entry Exit Exit Date]) And [Entry Exit Entry Date]<=[Report Start Date];(DaysBetween([Report Start Date];[Report End Date])+1);If(IsNull([Entry Exit Exit Date]) And [Entry Exit Entry Date]>=[Report Start Date];(DaysBetween([Entry Exit Entry Date];[Report End Date])+1);If([Entry Exit Exit Date]<=[Report End Date] And [Entry Exit Entry Date]<=[Report Start Date];DaysBetween([Report Start Date];[Entry Exit Exit Date]); DaysBetween([Entry Exit Entry Date];[Entry Exit Exit Date]))))))