Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

catalyst_75
New Contributor

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

Tags (2)
1 Solution

Accepted Solutions

Re: Just need confirmation

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
3 Replies
catalyst_75
New Contributor

Re: Just need confirmation

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?

Re: Just need confirmation

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
New Contributor

Re: Just need confirmation

Thanks

Community Browser