topic Re: null count in QlikView

null count

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have KPi object

if(today()>duedate,1,0) as DueDateFlag, ----- this expression in script used.

KPI expression:

ount({<DueDateFlag={1},iD-={'3','4'}>}Number)

here logic is greater than due date from today values should count but I have 8 null values it is not counting so how to include that?

Re: null count

YoussefBelloum — Fri, 27 Apr 2018 11:09:17 GMT

Hi,

you have null values on which fields ?

Re: null count

soniasweety — Fri, 27 Apr 2018 11:26:06 GMT

due_date field I have null

Re: null count

sunny_talwar — Fri, 27 Apr 2018 11:29:20 GMT

May be try to change your flag to this

If(Today() > Alt(duedate, Today()-1), 1, 0) as DueDateFlag,

Re: null count

YoussefBelloum — Fri, 27 Apr 2018 11:33:34 GMT

try to change dueflagdate line on the script to this, and use the same expression:

=if(today()>duedate or len(trim(due_date))=0,1,0) as DueDateFlag

Re: null count

soniasweety — Fri, 27 Apr 2018 11:47:57 GMT

still I can see the result with 2less count

actual 53 in Database and in qlik it is showing 51

dB query :

select count(number) from Main_Tbl

where State <> 3 and State <> 4 and GETDATE()>due_date

Re: null count

YoussefBelloum — Fri, 27 Apr 2018 11:50:47 GMT

did you try to uncheck all "suppress null values" options and see if these two lines appears ?

Re: null count

sunny_talwar — Fri, 27 Apr 2018 11:50:59 GMT

Can you check what GetDate() is giving

Select GETDATE()

From Dual;

and then see what Today() is giving you in QlikView by creating a text box object with =Date(Today())

Are you getting the same values?

Re: null count

soniasweety — Fri, 27 Apr 2018 12:02:57 GMT

DB result : 2018-04-27 17:31:27.733

qlik : 4/27/2018

Re: null count

sunny_talwar — Fri, 27 Apr 2018 12:04:15 GMT

And due date is not a timestamp, right?

Re: null count

soniasweety — Fri, 27 Apr 2018 12:08:47 GMT

end date is timestamp

12/29/2017 2:10:30AM

Re: null count

sunny_talwar — Fri, 27 Apr 2018 12:09:59 GMT

end date? what do we need end date for?

what about the due date? is that a timestamp also?

Re: null count

soniasweety — Fri, 27 Apr 2018 12:11:01 GMT

sorry sunny due date only its timestamp only

Re: null count

sunny_talwar — Fri, 27 Apr 2018 12:12:39 GMT

Then may be do this

If(Now() > Alt(duedate, Today()-1), 1, 0) as DueDateFlag,

or your DB check should be by truncating the duedate to just date based on your requirement

Re: null count

sasiparupudi1 — Fri, 27 Apr 2018 12:22:56 GMT

DueDate='2018-04-27 17:31:27.733'

May be try

=If(IsNull(DueDate) OR(Today()>Date(Floor(Timestamp#(DueDate,'YYYY-MM-DD hh:mm:ss.fff')))),1,0)

Re: null count

soniasweety — Fri, 27 Apr 2018 12:35:50 GMT

this gave correct result

If(Now() >due_date, 1, 0) as DueDateFlag,