topic Re: Expression in QlikView

Expression

Wed, 11 Nov 2015 09:30:37 GMT

I am new to QlikView.

I am trying to write an expression that returns the number of due date that are grater or equals today's date,but the expression is returning wrong information. This is the expression

=Count(if([Closing date]>= Today(2),[Candidate FullName]))

Thanks and Regards

Priscilla

Re: Expression

sunny_talwar — Wed, 11 Nov 2015 09:59:30 GMT

May be you need a distinct in there:

=Count(DISTINCT If([Closing date]>= Today(2), [Candidate FullName]))

Re: Expression

Gysbert_Wassenaar — Wed, 11 Nov 2015 10:01:41 GMT

Without more information we won't be able to give you an answer, just guesses. Can you post a small qlikview document or that demonstrates the problem? See this document for more information: Preparing examples for Upload - Reduction and Data Scrambling

Re: Expression

jonathandienst — Wed, 11 Nov 2015 10:07:05 GMT

First off, you should probably use Today() or Today(1). Secondly, is [Closing date] a proper (numeric) date field, or is it a string? Numeric value will right align by default in a list box, strings will left align.

If its a string, convert it to a date during the load using:

Date(Date#([Closing date]. '<format of closing date>')) As [Closing date],

where <format of closing date> is the format of the field in your source, eg 'YYYY-MM-DD' or 'D MMM YYYY'

Re: Expression

hic — Wed, 11 Nov 2015 10:11:58 GMT

If you use

Count(distinct if([Closing date]>= Today(2),[Candidate FullName]))

the QIX engine will count combinations of [Closing date] and [Candidate FullName]. In other words, if a candidate is associated with several closing dates it will always count the wrong thing.

Better then to use Set Analysis, which will count the right thing:

Count(distinct {$<[Closing date]={">=$(=Today(2))"}>} [Candidate FullName]))

HIC

Re: Expression

Fri, 13 Nov 2015 08:19:23 GMT

Hi Henric

I did try the set Analysis you gave me,but it is giving me wrong results,I have a list box of closing dates and i have a text box that is suppose to shows number of closing date from today onward,when ever i click on the text box i want to see the closing date from today onward on the closing dates list.

lease advise

Thanks and Regards

Priscilla

Re: Expression

hic — Fri, 13 Nov 2015 08:47:09 GMT

The Set Analysis expression will calculate a number and display this. So if you use it in a text box, you will see this number here. If the number isn't correct, then you need to post the qvw file so that we can look at what goes wrong.

Clicking on the text box will not make any selections, so you cannot change what you see in the list box this way.

HIC

Re: Expression

Fri, 13 Nov 2015 09:34:31 GMT

Download Priscilla's recrit.qvw

please check the link above the text box that is suppose to show the number is the due count.

Thanks

Re: Expression

hic — Fri, 13 Nov 2015 11:20:40 GMT

I don't want to download an exe from an unknown web site. Can you not just attach the file to your answer? ("Use advanced editor" then "Attach")

HIC

Re: Expression

Fri, 13 Nov 2015 12:04:00 GMT

I have attache the QV file

Thanks

Re: Expression

hic — Fri, 13 Nov 2015 16:39:42 GMT

I think your problem is in the data model. It looks like the following:

You have a synthetic key that might cause problems.

I suspect that all the candidates are found in the ZAK_RECRUTMENT, and if so you should not load the Candidat Spec table. Just remove the Load that loads this table. Or name the "Spec" from this table something else than "Spec".

HIC

Re: Expression

Mon, 16 Nov 2015 07:55:16 GMT

Thanks a lot

you are right the problem in in the data model.