Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Using a Null Field in an Expression

HI everyone, I'm hoping you can help, I have data that look likes below:

OrderIDOrderDateShipDate
11/1/20153/5/2015
21/30/2015
32/5/20153/10/2015
46/5/20156/10/2015
56/7/20157/5/2015
67/1/2015

What I am trying to do, is looking at a specific month, I want to see all orders ordered before that month, that could have shipped the current month but didn't.

For Example:

When looking at February - I have one order that was ordered before that month, but didn't ship the month it was ordered (which is a separate table in my App) and it wasn't shipped in February. Because it is a null field, if I select a ShipMonth of February it would go away, and if I select an OrderMonth of February, it would go away. However I DON'T want the order in July to appear in this selection, because it didn't have the potential to ship in February.

So if I selected August. both the order in February and the Order in July should show up.

How do I get this to work?

Right now I have:

=IF(IsNull(ShipDate) and (Month(OrderDate)<GetCurrentSelections(ShipMonth)), OrderDate)

If I eliminate the third part of the IF Statement, and keep it to just:

=IF(IsNull(ShipDate), OrderDate)

I get every order that hasn't shipped, but I really need to make the Month selection and look at everything that was ordered prior to the month I select.

1 Solution

Accepted Solutions
Not applicable

Re: Using a Null Field in an Expression

We actually ended up creating a Flag (0 or 1) to show whether or not it has a piece of data or not - and then used set analysis to show only the fields that did or did not have information.

4 Replies

Re: Using a Null Field in an Expression

Nulls are a pain to work with so the right thing to do is to replace them with values that can be selected. Once you've done that it becomes a lot easier to show the orders that didn't ship in the selected month or before. See attached qvw.


talk is cheap, supply exceeds demand
MVP
MVP

Re: Using a Null Field in an Expression

When you select february, shouldn't also IDs 1 and 3 be shown together with 2, because all 3 orders where ordered february or before, but not delivered until end of february.

I would assume, that a NULL in ship date would need to be regarded same as a ship date later than february when focusing on the question 'shipped until selection'. Maybe I am wrong, of course.

If you are in fact interested in something like this, attached an approach for that kind of analysis.

Arjunarao
Honored Contributor II

Re: Using a Null Field in an Expression

Hi altoole105

Please select Correct Answer.

Not applicable

Re: Using a Null Field in an Expression

We actually ended up creating a Flag (0 or 1) to show whether or not it has a piece of data or not - and then used set analysis to show only the fields that did or did not have information.

Community Browser