4 Replies Latest reply: Jul 27, 2017 12:54 AM by Fabrizio Giorgio RSS

    Nested IF works in expression field but not a Multibox Filter

    Fabrizio Giorgio

      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

        • Re: Nested IF works in expression field but not a Multibox Filter
          Prashant Sangle

          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

          • Re: Nested IF works in expression field but not a Multibox Filter
            Fabrizio Giorgio

            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