Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
catalyst_75
Contributor II
Contributor II

Just need confirmation

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 Management140.4618.03158.49
Incident Only8.670.008.67
Not Specified3.670.003.67
TCM - Hourly244.0715.76259.84
Telephonic Case Management42.830.9543.77
Tracking48.000.0048.00
TRIAGE-CS-LEVEL 25.670.005.67
TRIAGE-CS-LEVEL 36.330.006.33
TRIAGE-HCSG-LEVEL 16.400.006.40
TRIAGE-HCSG-LEVEL 27.730.007.73
TRIAGE-HCSG-LEVEL 37.070.057.12
TOTAL AVG 49.54 1.86 51.40

This is what it is currently spitting out

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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]).


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
catalyst_75
Contributor II
Contributor II
Author

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

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]).


talk is cheap, supply exceeds demand
catalyst_75
Contributor II
Contributor II
Author

Thanks