topic expression issue in QlikView

expression issue

Wed, 25 Nov 2020 16:16:04 GMT

hello guys,

can you help on below matter? This below is the expression using causing the issue.

=If(Num#(Time#(interval(CLOSEDDATEFORMAT) - Interval(CREATETS),' d h:mm:ss')) - HolidayCount < 0,OPEN_DAY_FRACTION,

(Num#(Time#(interval(CLOSEDDATEFORMAT) - Interval(CREATETS),' d h:mm:ss')) - HolidayCount))

Note1: the first record from the below table is cauing the issue bcz of holidaycount.

Note 2: Input & output (looking for

EXPECTEDOUTPUT

column information) :

Data:

QID	CREATETS	OPEN_DAY_FRACTION	CLOSEDDATEFORMAT	CLOSED_DAY_FRACTION	HolidayCount	TASK_DURATION	EXPECTEDOUTPUT
QWS170885043460	13/04/2017 09:27:15	0.606087963	14/04/2017 09:35:37	0.3997337963	1	0.0058135706	1.0058135706
QWS170889445470	13/04/2017 21:40:55	0.096597222	14/04/2017 10:23:21	0.4328819444	1	0.0965972222	0.096597222
QWS171354406609	06/06/2017 17:14:26	0.281643519	07/06/2017 12:23:03	0.5159953704	0	0.7976444	0.7976444
QWS170881595567	12/04/2017 23:52:36	0.005138889	19/04/2017 12:23:12	0.5161111111	4	2.5212500000	2.5212500000
QWS170544317729	04/03/2017 23:59:38	0.000266204	06/03/2017 16:28:28	0.6864351852	1	0.6867013889	0.6867013889

Re: expression issue

vinieme12 — Fri, 25 Aug 2017 14:52:02 GMT

try

=If((interval(CLOSEDDATEFORMAT- CREATETS,'d h:mm:ss') - HolidayCount) < 0,

OPEN_DAY_FRACTION,

interval(CLOSEDDATEFORMAT- CREATETS,'d h:mm:ss') - HolidayCount)

Re: expression issue

Fri, 25 Aug 2017 14:57:10 GMT

not working Pujari,thanks.

Re: expression issue

vinieme12 — Fri, 25 Aug 2017 14:59:00 GMT

can you post sample app or excel?

Re: expression issue

Fri, 25 Aug 2017 15:03:56 GMT

attached.

Re: expression issue

vinieme12 — Sat, 26 Aug 2017 07:35:15 GMT

Try

=if(((CLOSEDDATEFORMAT-CREATETS)-HolidayCount)<0

,OPEN_DAY_FRACTION

,(CLOSEDDATEFORMAT-CREATETS)-HolidayCount)

Re: expression issue

vinieme12 — Sat, 26 Aug 2017 07:39:28 GMT

Check the attached

Re: expression issue

Mon, 28 Aug 2017 10:07:49 GMT

hello pujari,

thanks for ur time,but still getting output as 0.00581xx instedof 1.00581xx.

i'm expecting the output as 1.00581xx for QWS170885043460.

kindly look into it.

Re: expression issue

vinieme12 — Mon, 28 Aug 2017 11:09:57 GMT

Can you derive that output in an excel and show me? as per your conditions 0.00581xx seems to be the correct answer!!

Re: expression issue

rohitraut — Mon, 28 Aug 2017 11:44:18 GMT

Try this:

if(Num#(Time#(interval(CLOSEDDATEFORMAT) - Interval(CREATETS),' d h:mm:ss')) - (HolidayCount <0) <0,OPEN_DAY_FRACTION,(Time#(interval(CLOSEDDATEFORMAT) - Interval(CREATETS),' d h:mm:ss')) )