topic Re: Just need confirmation in QlikView

Just need confirmation

catalyst_75 — Fri, 25 Jul 2014 13:57:38 GMT

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

Re: Just need confirmation

catalyst_75 — Fri, 25 Jul 2014 14:01:39 GMT

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

Gysbert_Wassenaar — Fri, 25 Jul 2014 14:13:46 GMT

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

Re: Just need confirmation

catalyst_75 — Fri, 25 Jul 2014 14:21:31 GMT

Thanks