topic Re: count if in QlikView

count if

samuel_brierley — Tue, 03 Sep 2013 09:22:01 GMT

count ({$< [Fault Cleared] = {"<=$=(Max([Date Detected]))"}>}[Fault Cleared]

Can someone explain what is wrong with this expression?

what its supposed to do is count all row where Fault Clearerd is less than the Max Date detected

Re: count if

swuehl — Tue, 03 Sep 2013 09:24:06 GMT

Could just be a mispositioned opening bracket and missing closing bracket:

count ({$< [Fault Cleared] = {"<=$(=Max([Date Detected]))"}>} [Fault Cleared] )

Re: count if

Tue, 03 Sep 2013 09:26:51 GMT

try this , you miss the = sign

count ({$< [Fault Cleared] = {"<=$(=Max([Date Detected]))"}>} [Fault Cleared] )

Re: count if

samuel_brierley — Tue, 03 Sep 2013 09:45:18 GMT

Nope niether of them are working.

Re: count if

samuel_brierley — Tue, 03 Sep 2013 09:50:09 GMT

Ive been trying with this for a while and im unsure why im finding it so difficult. all it needs to do is count all record with the Fault Cleared is less than the max Date detected.

Re: count if

swuehl — Tue, 03 Sep 2013 09:56:47 GMT

Nope niether of them are working.

As you probably know from your work as a developer, "not working" is not a good issue description...

Well, start with checking if the dollar sign expansion results in something useful, put

=Max([Date Detected])

into a text box.

What do you get?

If you get something, it's probably a date, right?

What is the format of that date value? What is the format of field [Fault Cleared] and what is your default date format?

Re: count if

samuel_brierley — Tue, 03 Sep 2013 10:09:12 GMT

sorry you are right not a good description but Im no developer, Im just trying to learn the tool for work, as you can understand its very difficicult for those of us who have no previous experience in this.

the formats are all the same and are "dd/mm/yyyy"

Ive used the text box to give max Date detected, max date cleared and count date cleared and count Date detected,

all give the correct number and in reagrd to dates in the correct format

Re: count if

nizamsha — Tue, 03 Sep 2013 10:12:10 GMT

count ({$< [Fault Cleared] = {"<=$(=Date( Max([Date Detected])))"}>} [Fault Cleared] )

Re: count if

swuehl — Tue, 03 Sep 2013 10:16:51 GMT

Next step would be to look at the full expression after the dollar sign expansion (e.g. by using a chart expression with no label, hovering over the label should show the evaluated expression then.

Sometime QV evaluates a date like DD/MM/YYYY as math expression (two divisions), which results in a very low number...

Try enclosing the date in single quotes:

=count ({$< [Fault Cleared] = {"<='$(=Max([Date Detected]))'" }>} [Fault Cleared] )

Re: count if

samuel_brierley — Tue, 03 Sep 2013 11:52:13 GMT

Im sorry ive looked at every part of the formula and all seems to work and is in the same format but i simply cant get the expression to work, would you be prepared to have a look at it? I dont have the required permission to open what gets sent back but would be eternall ygrateful for inswtructions.

you an absolute star

Dropbox - V9.2.qvw

I completly understand if ur not willing to download it

Re: count if

swuehl — Tue, 03 Sep 2013 12:01:37 GMT

Field Fault Cleared is not recognized as a timestamp by QV, because it does not match the default timestamp format:

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

It's format is indeed

'DD/MM/YY hh:mm:ss'

25/01/13 20:07:02

It's best to handle that in the load script, either by correting your default timestamp format, or by interpreting the value on load:

LOAD

....

timestamp#([Fault Cleared],'DD/MM/YY hh:mm:ss') as [Fault Cleared],

...

Re: count if

samuel_brierley — Tue, 03 Sep 2013 12:11:20 GMT

I LOVE YOU!!!!!!

Re: count if

antose — Tue, 03 Sep 2013 12:22:11 GMT

Help one of my customer with similar problems last week

Count({$< MatDatum={"<$(=Date(Today(), 'YYYYMMDD'))"} >} NyckelHuvudMITPLO)

So in you case can this works?

Count({$< [Fault Cleared] = {"<=$(=Date(Max([Date Detected]), 'DD/MM/YYYY'))"} >} [Fault Cleared])