Nested IF works in expression field but not a Multibox Filter

[Anonymous]

Hi all,

Would appreciate some suggestions on this one...

I have a qlikview sheet that tracks customer order due dates vs supplier purchase order due dates.

i.e: that a product we purchase from our supplier will be in our warehouse on time to supply a customer order due date.

I have an expression in my pivot chart that flags orders that orders that are "Late", "On Track" and 'N/C' if there is no link between a Purchase order and a Sales order.

the expression field includes a 10 day buffer so Customer order due date - 10days is when the product should be in from our supplier at the very latest.

Here's my expression field:

IF(POCPODueDate>(POCSOShipDate -10), 'Late',

IF(POCPODueDate<(POCSOShipDate -10), 'On Track',

IF(IsNull(POCPODueDate>POCSOShipDate),'N/C')))

This works fine, I even have on track showing Green and Late showing in Red background colours, however, I want to create a filter to display only Late or On Track or N/C lines.

I usually use a multibox and put the expression in there, however for some reason it's giving me incorrect lines... which is strange seeing that the expression field works fine... it must be that for some reason the multi box is not processing the mathematical formula in the same way the expression is...

Is there an easier way to filter the chart based on those expression variables? Or on the background colour of a cell?

I'm attaching a partial screen shot of my expression field for better visualisation... I cannot show other data as it is confidential unfortunately...

Hoping someone is able to advise...

Thanks!!

Fabrizio Giorgio

[PrashantSangle]

If POCPODueDate and POCSOShipDate are not derived field and coming from same table then create flag in script and use that flag in front end.

Regards,

Prashant Sangle

[Anonymous]

Hi Prashant,

Thanks for your reply...

POCPODueDate and POCSOShipDate are coming from the same table (i've joined several table from an ODBC database for this report) - see attached...

What do you mean by create flag in the script? There were cases in other reports where I had to do mathematical calculations in the script to create new fields that I would use in the front end, but I have never used an expression with a logical outcome... which is what I think you are referring to...

Can you give me an example so I can understand?

Thanks again,

FGiorgio

[PrashantSangle]

Hi,

In your script add below

IF(POCPODueDate>(POCSOShipDate -10), 'Late',

IF(POCPODueDate<(POCSOShipDate -10), 'On Track',

IF(IsNull(POCPODueDate>POCSOShipDate),'N/C'))) as remark_flag

Use remark_flag in front end, Instead of expression.

Regards,

[Anonymous]

Thanks Prashant,

this is a great feature which I didn't know existed..

Only been using qlikview for about 6 months or so...

it took a bit of experimenting and I ended up setting it up a 2 dimension multibox, for some reason the when selecting N/C I was getting some of the others as well, which I havent really understood why yet...

what I ended up using is:

1: Setup an expression in a multibox filters out all the N/C: (not committed)

=IF(POCPOLineQtyCommit > 0,'Committed QTY Exists', 'No Committed QTY')

- Where there is no Committed QTY, POCPOLineQTYCommit will be NULL "-"...

Then for all entries where  there is a value in POCPOLineQtyCommit, I setup a Flag in the script connecting to the Master Table via POCPOrder field as per below:

POCDueDateStatus:

Load

POCPOrder,

IF(POCPODueDate<=(POCSOShipDate-10),'On Track',

IF(POCPODueDate>(POCSOShipDate-10),'Late'))as POCDueDateStatus Resident POCMaster;

Then I used POCDueDateStatus in my multibox as the second selection...

I have tried a few ways to combine these two selections in the script, but haven't been able to as yet,

I have tried:

POCDueDateStatus:

Load

POCPOrder,

IF(ISNULL(POCPOLineQtyCommit)=1,'N/C',

IF(POCPODueDate<=(POCSOShipDate-10),'On Track',

IF(POCPODueDate>(POCSOShipDate-10),'Late')))as POCDueDateStatus Resident POCMaster;

Can you think of anything I could try to combine these 2 expressions in the script?

Thanks again...

Fab