11 Replies Latest reply: Jan 6, 2014 7:46 AM by Leni Balakrishnan RSS

    Convert If condition in set analysis

    Leni Balakrishnan

      Hi All,


      I would like to convert the below if-else condition in set analysis.


      sum(if(It.isAdj and It.QTY=0,0,It.Qty * It.factor))


      I tried to convert as below using set analysis but it is not giving the result as the above expression gives. The set analysis I tried is:

      sum({<It.isAdj = {0},It.QTY-= {0}>}It.Qty * It.factor)


      This set analysis is giving the result equal to the below expression:

      sum(if(not It.isAdj and It.QTY> 0,It.Qty * It.factor))


      So I need to convert this sum(if(It.isAdj and It.QTY=0,0,It.Qty * It.factor)) expression to set analysis where both should give me the same result.


      Can anyone help me in this?



      Leni Balakrishnan

        • Re: Convert If condition in set analysis
          vikas mahajan



          Pleae find expr using if condition in set analysis



          fabs(SUM(AGGR(if(((vSelectDate - max({$<StockInOutIndicator={1}>}PostingDate) >=271)),SUM(Inv_Val)),Location_Code, Item_Code, [Lot No_]))/100000)




          • Re: Convert If condition in set analysis

            Hi Leni ,


            Your Expressions on Set Analysis are perfect .. U don't need to change into Set analysis ..

            Those are perfect.



            koti Reddy

              • Re: Convert If condition in set analysis
                Leni Balakrishnan

                But it is not giving me the exact result of the if condition.


                sum(if(It.isAdj and It.QTY=0,0,It.Qty * It.factor)) in set analysis = sum({<It.isAdj = {0},It.QTY-= {0}>}It.Qty * It.factor) but both returns different value.

                  • Re: Convert If condition in set analysis

                    = sum({<It.isAdj = {0},It.QTY-= {0}>}It.Qty * It.factor)  it is not giving the same value because

                    the expression says if   It.isAdj = {0} and It.QTY-= {0} (except 0 for remaining all Values )  the result would be

                    this ---> It.Qty * It.factor

                    And it is not equal to this----> sum(if(It.isAdj and It.QTY=0,0,It.Qty * It.factor))

                    there are lot of conditions if both are zeros then the expression should Be 0 . So for  your situation it is better to use same if expression ..

                    • Re: Convert If condition in set analysis
                      Leni Balakrishnan

                      Why I am converting this expression using set analysis is,


                      I have two charts X & Y where the expression & filters for both the charts are same except one which is Cust. Name. Chart X should show details of all customers but Chart Y should show only a Customer details.


                      So I thought of converting the expression so that Chart X will not change for the selection on Customer Name.


                      Kindly suggest me an idea or your opinion to achieve this scenario



                        • Re: Convert If condition in set analysis

                          Until there is relationship between dimensions(Cust , Custname) in datamodel it is obviously changes based on filter selections


                          If u want to not affect the selections of Chart X  in Chart Y .. Then Create Alternate States and Provide Separate filters


                          and apply Alternate states to that particular Objects..


                          Hope it helps..

                            • Re: Convert If condition in set analysis
                              Leni Balakrishnan

                              Hi Koti,


                              Thanks for your suggestion.


                              I am not familiar with the Alternate states concept. Could you please explain me on how it works?.

                              Before that let me explain my full requirement here so that you could guide me properly.


                              I need to send chart X & Y in mail through excel attachment and it will not be available for the enduser through portal. It is used only to send mail.


                              I have a .bat file which I have scheduled through Windows Task scheduler to trigger the mail on a particular time. When the .bat file is triggered it will open my application, make the required filters, export the data to excel from those charts, save the excel file, the saved excel file is attached to the mail and sent to the requested users.


                              This is my requirement and in this I have difficulty in applying the filters to chart which I explained above.



                                • Re: Re: Convert If condition in set analysis

                                  Hi Leni,

                                  Alternate States Available only  from Qlikview 11 Onwards..

                                  Go to Document Properties -->General--> Alternate states->Click On It-->Click on Add->Enter Statename(Some String) State1-> Ok  Like that Create Two states..


                                  Go To Object Properties..Provide Statename to All the Objects which U want filters for First chart


                                  Do the Same for Second Chart And You can Make Selections for charts you are showing...


                                  Otherwise You Can Detach the Chart from Dashboard Right Click on chart and Click On Detach the Chart.. This Also does not effect the Selection of Filters into Chart.


                                  May be It is perfect for Ur Problem..


                                  If U want to check How Alternate States work ...In Qlikview 11 Developer-->Open Whats new in Qlikview11 Dashboard..

                                  Product Grouping Tab..


                                  May be It will not be What r u looking for.. It will help to Achieve it....



                                  Check Out This Vedio  Comparative Analysis - YouTube


                                  And PFA.  I hope  It Helps...

                        • Re: Convert If condition in set analysis
                          BRIJESH GADHESARIYA

                          Why you want to convert into set analysis?


                          It seems you do not need to convert it. . . Expression you used is fine!


                          Set Analysis is used when you wanted to give explicit selection on a field(even though you don't select from front end)