14 Replies Latest reply: Feb 28, 2018 12:59 AM by Jonathan Dienst RSS

    IF AND Expression

      Hello,

       

      I have some criteria which consists of the following

       

      G/L Number

      10040,10041,10045,10050,10051,10055,10060,10061,10065,10100,10101,10105

      Job No.

      Industry Code

       

      The idea is that:

      IF G/L Number = 10040,10041,10045

      AND Job No. -='' " (does not equal)

      AND Industry Code =" "

      SUM(Amount)

       

       

      Any Ideas on how I can tackle this would be highly appreciated

       

      Thank you in advance for your time

       

      Mark

        • Re: IF AND Expression

          try this

           

          if((GLnum=10040 or GLnum=10041 or GLnum=10045) and Jobno<>'' and Industrycode='',Sum(Amount),0)

            • Re: IF AND Expression
              John Witherspoon

              As asked, the above looks correct, though you can avoid the ORs by using a match():

               

              if(match(GLnum,10040,10041,10045) and "Job No."<>' ' and "Industry Code"=' ',sum(Amount))

               

              However, I suspect that what you really want to do is sum up the Amounts of all records where those conditions are true.  For that, you do the if() inside of the sum():

               

              sum(if(match(GLnum,10040,10041,10045) and "Job No."<>' ' and "Industry Code"=' ',Amount))

               

              And if you want it to perform well, you use set analysis instead:

               

               

              sum({<GLnum*={10040,10041,10045},"Job No."-={' '},"Industry Code"*={' '}>} Amount)

                • IF AND Expression

                  Hi John,

                  I want to learn what does this expression mean

                  sum({<GLnum*={10040,10041,10045} ....)

                  I wish to know the significance of the asterisk * used. Wouldn't this expression work without the asterisk *.

                   

                   

                  Thanks,

                  Bikash Debnath

                    • IF AND Expression
                      John Witherspoon

                      The *= specifies intersection, or as I think of it, it restricts my selections to no more than the indicated values.  It's the same behavior as a sum(if()).  I can still select just 10040, and I'll then only see 10040.  If I select 12345, the chart will not display data, which in some cases may help prevent confusion about what the user is looking at.  I typically use the *= as I feel it is a more natural, more normal behavior than the =, which is a pure, static override.

                        • IF AND Expression

                          Thank you John,

                          You really explain well. Love it!

                          I understood the idea.

                           

                           

                          with best regards,

                          Bikash Debnath

                          • Re: IF AND Expression
                            ar eiuiew

                            Hi Mate

                            I have a similar issue with if in the chart expression:

                             

                            My requirement is basically this:

                            Chart Dimension: Mth (like Jan, Feb , ...)

                             

                            For Current month comparing due date of each ID with Today() and counting the overdued IDs

                             

                            For past months, comparing the due date of each ID with LastDayOfTheMonth (Which is a master calendar field date I added)

                            This is my expression which is not working:

                             

                            If (Mth=date(Today(),'MMM'), Count(  {<DueDate={"<=$(=Today())"}>} Comp_ID),Count(  {<DueDate={"<=$(=LastDayOfTheMonth)"}>} Comp_ID ))

                        • IF AND Expression
                          Josh Duenyas

                          Am I the only one or are there others who experience the same problem:

                          in a set analysis, if using  -= or *= the intelisense (autocomplete) stops working.

                           

                          Thanks

                           

                          Josh Duenyas

                            • Re: IF AND Expression
                              Jonathan Dienst

                              >>the intelisense (autocomplete) stops working.

                               

                              That was a problem on versions of QV prior to 11.20 SR7 or SR8 (I think that was about 2 years ago). You may want to upgrade to a more recent version.

                                • Re: IF AND Expression
                                  ar eiuiew

                                  Hi Jonathan,

                                  Hope you're well. Could you please have a look at my expression :

                                  My requirement is basically this:

                                  Chart Dimension: Mth (like Jan, Feb , ...)

                                   

                                  For Current month comparing due date of each ID with Today() and counting the overdued IDs

                                   

                                  For past months, comparing the due date of each ID with LastDayOfTheMonth (Which is a master calendar field date I added)

                                  This is my expression which is not working:

                                   

                                  If (Mth=date(Today(),'MMM'), Count(  {<DueDate={"<=$(=Today())"}>} Comp_ID),Count(  {<DueDate={"<=$(=LastDayOfTheMonth)"}>} Comp_ID ))

                          • IF AND Expression
                            Josh Duenyas

                            This will do:

                             

                            =Sum({<[G/L Number]={10040,10041,10045},[Job No.]-={'',NULL},[Industry Code]={'',NULL}>} Amount)

                            • IF AND Expression
                              Rahul Gupta

                              Hi,

                               

                              sum(if(match(GLnum,10040,10041,10045) and Isnull(JobNo.) and Isnull(IndustryCode),Amount))

                               

                              Regards

                              • Re: IF AND Expression

                                Hello everyone!,

                                 

                                Thank you for your answers. However, syntactically they seem ok, I am not getting any results. I believe this may be because of the tables being dependant of each other. (i'm not 100% sure how Qlikview works) I've attached an image below

                                 

                                Qlikview.jpg

                                so as you can see. IF the there is a Job Number it will look for a customer number. IF there is not a customer number the industry code will obviously be blank (as it cannot find the link). However (please correct me if im wrong), Qlikview will not do this check because there is no link between customer and industry.

                                 

                                I made the assumption that it see's it as blank regardless.

                                 

                                Thanks again

                                 

                                Mark

                                  • IF AND Expression
                                    susant Kumar swain

                                    So by seeing your cloud you should join the jobcode from the GL table with Jobcode in Job table

                                     

                                    even if the column is null so by default nulls will not be joined in qlikview so using the variable

                                     

                                    NullAsValue JobNo; Will connect the tables even if the fields have null value by this way

                                     

                                    u can see the data