Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I am trying to use the count function to count the number of orders that do not have null value in its invoice date. I have tried a lot of what was already on the community like:
=cout(distinct if(len(invdate)>0, key));
= count( {$} KEY)
= count(distinct if(isnull(INVDATE)=0, key));
I am running out of ideas. Yet if I use the expression: = IF(INVDATE='', NOINVOICE), that works well proving that the QV identifies the missing date.
Anyone?
Hi adjacentrock,
Try this expression,
count(distinct if(len(trim(invdate))>0, invdate))
I tested, it works for me.
The trim() is important in this expression, because even if the invdate only contains some spaces, the len() will return a nonzero value. So trim() removes the spaces in empty invdate if it has any.
Regards
Hi,
thanks but it still doesnt work, also, the heading of my question is not really correct as I am trying to count records that have null values in their invoice date.
Can you upload your data file with merely the invoice date field? Just a fraction should be fine.
silly question where do you insert qv docs in qlikcommunity? am sending it to you by email in the mean time
Sent: Tuesday, 29 November 2011 3:12 PM
To: Danny Rock
Subject: - Re: Counting null values in a table
QlikCommunity<http://community.qlik.com/index.jspa>
Re: Counting null values in a table
created by quickview<http://community.qlik.com/people/quickview> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/168589#168589
hi
try this
=if(isnull(invdate)='-1',count (invdate))
may it helps you
thanks
rohit
Not working either, it just ignores every order in the count function.
Sent: Tuesday, 29 November 2011 3:48 PM
To: Danny Rock
Subject: - Re: Counting null values in a table
QlikCommunity<http://community.qlik.com/index.jspa>
Re: Counting null values in a table
created by rohit214<http://community.qlik.com/people/rohit214> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/168579#168579
No its not working either. It is so strange because if i put the expression:
IF(ISNULL(INVOICEDATE)='-1','NO INVOICE'),
it knows that it is empty and shows the NO INVOICE where there really is not any invoice, but then when is put the same condition in the the count function, it ignores all in the count
Hi,
Hope this helps you in solving your problem
=Sum(if(isnull(invdate) OR Len(invdate) = 0, 0, 1))
Also this will improve the performance than using the count.
Regards,
Jagan.