How to fix "Infinity" and "Nan"

Jeff S 1 Reputation point
2020-08-29T16:22:25.683+00:00

I am getting "Infinity" or a "NaN" value on my expression below. Suggestions on my expression to improve it?

=Sum(CDbl(IIF(Fields!Type.Value = "Charges", Fields!PatientAmount.Value + Fields!InsuranceAmount.Value, 0))) /(CountDistinct(iif(Fields!IsEncounter.Value = 1, Fields!PatientVisitId2.Value,Nothing)) - COUNTDISTINCT (IIF(Fields!IsEncounter.Value = -1, Fields!PatientVisitId2.Value,Nothing)))

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,935 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 37,296 Reputation points
    2020-08-31T02:42:19.347+00:00

    Hi,

    In the denominator "CountDistinct(iif(Fields!IsEncounter.Value = 1, Fields!PatientVisitId2.Value,Nothing))"

    In this expression, if the Fields!IsEncounter.Value≠1, it will be CountDistinct(nothing)=0, zero cannot be used as a divisor. So there will be "Infinity" and "Nan".

    I assumed that you're trying to do calculations on the entire data.

    So you should use the expression on a single textbox instead of a column.

    If I misunderstand, please check the needs you want and then we can improve it.

    If you have any question, please feel free to let me know.

    If your problem has been solved, please mark "accepted answer" on my reply, thank you for your understanding.

    Regards,

    Zoe

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.