Using Interval in Set Analysis

[crichter14]

My power user has a calculation that works fine in his table, but not in his KPI.  I'm working out an option to do the interval in the load script, but is there a way to put it in the Set Analysis instead?  Qlik Sense solutions only please.

Thank you!

Cheryl

IF(INTERVAL([SHIPTRACK SHIPMENT COSTING COMPLETION DATE] - [SHIPTRACK INVOICE RECEIVED DATE],'D')<0,

IF(INTERVAL([SHIPTRACK SHIPMENT COSTING COMPLETION DATE] - [SHIPTRACK INVOICE RECEIVED DATE],'D')>20,0,

avg({<

[SHIPTRACK CURRENT STATUS]={'DELIVERED'}

>}

date([SHIPTRACK SHIPMENT COSTING COMPLETION DATE])
-
date([SHIPTRACK INVOICE RECEIVED DATE])
)

))

[agigliotti]

Hi @crichter14 ,

What is the business logic behind the KPI?
What are you trying to achieve?
Please explain more.

[crichter14]

In theory, an invoice should always be received before its completed.  But not always-users make typos - or delays happen - they don't want to count things that skew the data based on set criteria. 

This user wants to exclude outliers where the difference is less than 0 or more than 20 days.

Then he wants to average all the intervals...so we know the average days received to completion was 7.49 for example.

I'd send real data, but our company does not approve of that.  Not trying to be vague.