Multiple if in expression

[clausbarlose]

Hi,

I am having difficulties with creating a listbox to select tickets from our management system.We look at our tickets in a monthly perspective and like to follow tickets created in a specific month or tickets closed in the same month. Users inputs StartDate and EndDate. A ticket always has a creation date but not always a closed date (ie. field is null() ).I have made a listbox with the following expression:

=if ( not isnull (TicketClosed),

if (  (TicketCreated >= StartDate and TicketCreated <= EndDate) or

   (TicketClosed >= StartDate and TicketClosed <= EndDate), 'Yes', 'No'),

  if (TicketCreated >= StartDate and TicketCreated <= EndDate, 'Yes', 'No'))

Listbox title is obvoiusly "Tickets created or closed in period".

Listbox shows the proper Yes and No, but I end up with all tickets but the ones that have no TicketClosed date, ie. TicketClosed is null().

Anyone with an opinion on this or event better a sugestion for doing it otherwise?

Btw - we are on QV 9

All the best!

Claus

[CELAMBARASAN]

Hi,

     You mean you didn't get the results for Ticked closed date is null?

Celambarasan

[clausbarlose]

When clicking Yes in the listbox I get the tickets where CreationDate or ClosedDate are in the correct interval, but are missing tickets that have a null() value in ClosedDate.

Tickets can be created a lot earlier in which case we like to track them by the ClosedDate.

The selection acts like a filter.

[CELAMBARASAN]

Hi,

     Check whether it has null value or Empty value.

Celambarasan

[clausbarlose]

Hi,

I took a couple of samples and QV says field is null(). I put this in the table view:

=if ( isnull(TicketClosed), 'Null()', 'Not Null()')

- and QV says 'Null().

     Claus

[CELAMBARASAN · Accepted solution]

Hi,

     Try this method

     Before loading the table use NullAsValue like below

     NullAsValue TicketClosed;

     Load

          *

     From Datasource;

In list box use expression as

=if ( TicketClosed<>'',

if (  (TicketCreated >= StartDate and TicketCreated <= EndDate) or

   (TicketClosed >= StartDate and TicketClosed <= EndDate), 'Yes', 'No'),

  if (TicketCreated >= StartDate and TicketCreated <= EndDate, 'Yes', 'No'))

Celambarasan

[#7]

I think the enclosed kindly provided by John Witherspoon could be usefull to you (you shall put it in the load). It basically lets you choose only one date regardsless of whether the ticket has started or ended there.

[clausbarlose]

Hi,

I'll try this and post a reply. Thanks for your help!

/Claus

[clausbarlose]

Hi - thanks for the post. Unfortunately this can't be done in the script, as user inputs start and end date in the GUI.

/Claus

[clausbarlose]

Hi Celambarasan

The NullAsValue did the job, thanks alot! - Funny though, that the table view said that value was null. Apparently QV in some cases has some challenges with nulls - or am I wrong?

Thanks DK_ASN too!

Best regards,

Claus