4 Replies Latest reply: Apr 14, 2013 5:40 PM by Alen Ianni RSS

    'IF' Function in Set Analysis

    Alen Ianni

      Hello everyone of the community,

       

      I started since few months using Qlikview as developer and even if I am reading a bunch of books and articles I see that there is a long learning path in front of me. However I am fascinated by Qlikview and I am glad that there is a forum like this full of enthusiastic users.

       

      I have a conceptual question regarding set analysis and the function IF.

      I tried an expresssion like the one below but the syntax seems not well taken and it produces no results ('-').

       

       

       

      Expression:

       

      sum ({1<


      if ([Order]='Billed',[Billing Date],[Order Date])={"$(=date([ReportingDate],'YYYYMM'))"}

      >}IF([Unit]='MT',[Order Quantity]*1000,[Order Quantity]))

       

      My question is a general one:

      Can I use nested IF in a Set Analysis?

       

       

      Apologies if I missed a past reply on this topic but I did not find it on the search.

      In this case how would much appreciate if you can re-direct me to the appropriate thread!

       

       

      Many Thanks for your support,

      Alen

        • Re: 'IF' Function in Set Analysis
          Gysbert Wassenaar

          What you're trying isn't going to work because the set is calculated first and then the dimensions and expressions of the chart are applied to the calculated set. The set is calculated once for the chart, not per row. So the if statement in the set modifier isn't going to work. If ReportingDate is a field then comparing it with either Billing Date of Order Date won't do what you expect either.

            • Re: 'IF' Function in Set Analysis
              Alen Ianni

              Thank you very much for your reply Gysbert,

              this comment gave me the idea of an alternative path to follow.

               

              I have added a new dimension Date as calculated by the IF function

              directly to the Fact table. Then I guess I will be able to use it in the set analysis but

              I could not try it all yet. I'll try tomorrow the rest in the office.

               

              Even if I have a pretty huge application I think is

              a good way to go and when all will work I will try reducing the size of it.

               

              Thank again!

              Alen

            • Re: 'IF' Function in Set Analysis
              Kaushik Solanki

              Hi,

               

                   Yes you can use if statement like shown below.

               

                  sum({<[$(=if(Status='A',Date1,Date2))] = {"<=$(=makedate(2012,10,01))"}>}Sales)

               

                   But for this there should be a selection in the Field Status.

               

                   If you dont select the status it will not work, but still you can use few more functions to avoid selection too.

               

              Regards,

              Kaushik Solanki

                • Re: 'IF' Function in Set Analysis
                  Alen Ianni

                  Hi Kaushik,

                   

                  thank you for your Reply. I m not sure I fully understand what you mean.

                   

                  When I try this the syntax automaitcally consider the "$(=if(Status" as Variable.

                  Is this what you mean to use a Variable?

                   

                  For the moment I am testing how it comes adding a dimension to my fact table with the field already

                  calculated at lines level. Not really a "saving Kbit" solution but I think is worth to check it.

                   

                  Best Regards,

                  Alen