Just need confirmation

[catalyst_75]

Here is just one line of code that I am doing an average on for days open. This will only calculate if there are dates in the field correct? Or is this calculating all the time and going to skew my number?

=SUM([Closed Date]-[Claim Date])/Count(DISTINCT [Claimant ID])       // Avd days 1

Case Type	Avg Days Open 1	Avg Days Open 2	Avg Days Open 3	TOTAL AVG DAY OPEN
Field Case Management	140.46	18.03		158.49
Incident Only	8.67	0.00		8.67
Not Specified	3.67	0.00		3.67
TCM - Hourly	244.07	15.76		259.84
Telephonic Case Management	42.83	0.95		43.77
Tracking	48.00	0.00		48.00
TRIAGE-CS-LEVEL 2	5.67	0.00		5.67
TRIAGE-CS-LEVEL 3	6.33	0.00		6.33
TRIAGE-HCSG-LEVEL 1	6.40	0.00		6.40
TRIAGE-HCSG-LEVEL 2	7.73	0.00		7.73
TRIAGE-HCSG-LEVEL 3	7.07	0.05		7.12
TOTAL AVG	49.54	1.86		51.40

This is what it is currently spitting out

[catalyst_75]

Or do I need to do IF statements or Modifiers on the date fields as to confirm that they are filled in before I can try and calculate an average to get accurate data?

[Gysbert_Wassenaar · Accepted solution]

If either [Closed Date] or [Claim Date] is null then [Closed Date]-[Claim Date] will be null too. But the Claimant ID will be counted. That could give you averages you're not expecting. If your expression is a chart expression you could modify it to =SUM([Closed Date]-[Claim Date])/Count({<[Closed Date]={'*'},[Claim Date]={'*'} >}DISTINCT [Claimant ID]).

[catalyst_75]

Thanks