topic Re: Using a Null Field in an Expression in QlikView

Using a Null Field in an Expression

Fri, 07 Aug 2015 20:58:53 GMT

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

OrderID	OrderDate	ShipDate
1	1/1/2015	3/5/2015
2	1/30/2015
3	2/5/2015	3/10/2015
4	6/5/2015	6/10/2015
5	6/7/2015	7/5/2015
6	7/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.

Re: Using a Null Field in an Expression

Gysbert_Wassenaar — Sat, 08 Aug 2015 17:23:20 GMT

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.

Re: Using a Null Field in an Expression

swuehl — Sat, 08 Aug 2015 18:00:53 GMT

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.

Re: Using a Null Field in an Expression

qlikviewwizard — Sun, 09 Aug 2015 04:51:13 GMT

Hi altoole105

Please select Correct Answer.

Re: Using a Null Field in an Expression

Tue, 17 Nov 2015 19:49:20 GMT

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.