Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nagarajs
New Contributor

Need help on calculating no. days between 2 dates

I'm trying to calculate avg Lead Time . Lead Close date - Lead State Date = Yield time.  This formula works in QV and not in QS. Can some help .

avg((Ceil(Interval(Date#(CLOSEDATE, 'MM/DD/YYYY hh:mm:ss TTT') - Date#(STAGEDATE, 'YYYY/MM/DD'), 'DD' )) ))

3 Replies
MVP
MVP

Re: Need help on calculating no. days between 2 dates

1. I would not use the function interval to calculate the difference in days - simply subtract one date from the other.

2. I'd recommend to convert string fields into dates (using the Date#() function) in the script and not in the expression - that's a waste of run-time resources.

With these two improvements in mind, the expression should look like this:

avg(Ceil(CloseDate-StageDate))

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

sasiparupudi1
Honored Contributor III

Re: Need help on calculating no. days between 2 dates

try

avg(ceil(Interval(floor(Date#(CLOSEDATE, 'MM/DD/YYYY hh:mm:ss TTT')) - Date#(STAGEDATE, 'YYYY/MM/DD'), 'DD' ))))

nagarajs
New Contributor

Re: Need help on calculating no. days between 2 dates

thank you