2 Replies Latest reply: Jan 9, 2017 3:00 PM by Rupam Das RSS

    Creating new dimension in load script

    Qlik User

      Hi,

       

      I'm currently trying to implement a bar chart/waterfall chart, using the Waterfall Chart extension here (Qlik Branch). With test data, where I have created a dimension to describe waterfall stage, I am able to achieve the below.

       

      Waterfall.png

      The actual data model does not have such a dimension however, so I will need to create it within the application.

       

      I have tried the following in the load script, but this will not work however as it will only produce one value, 'New Deals to Pipeline' (Corresponding to the first if statement).

       

      if(WildMatch([OPRT_TYPE],'New*') AND [UNIQUE_OPRT]= '1'
          ,'New Deals to Pipeline'
          ,
          if(WildMatch([OPRT_TYPE],'New*') AND [UNIQUE_OPRT]= '1' AND Year(NEW_CLOSE_DATE)='2016' AND [WLP]='Won'
          ,'Won Deals'
          ,
          if(WildMatch([OPRT_TYPE],'New*') AND [UNIQUE_OPRT]= '1' AND Year(NEW_CLOSE_DATE)='2016' AND [WLP]='Lost'
          ,'Lost Deals'
          ,
          if(WildMatch([OPRT_TYPE],'New*') AND [UNIQUE_OPRT]= '1' AND Year(NEW_CLOSE_DATE)='2016' AND (COMMIT_ML_OTHER='Commit' OR COMMIT_ML_OTHER='Most Likely' OR COMMIT_ML_OTHER='Early')
          ,'Pipeline (Today)'
          )))) as WaterfallIndicator,
      

       

      How should I go about achieving this?

       

      Qlik User

        • Re: Creating new dimension in load script
          Ugonna Okoli

          You want the more complex criteria to be evaluated first because deals will be placed in the first category they pass it's criteria. Try changing the sequence of the if statements to something like this;

           

          if(WildMatch([OPRT_TYPE],'New*') AND [UNIQUE_OPRT]= '1' AND Year(NEW_CLOSE_DATE)='2016' AND [WLP]='Lost'

              ,'Lost Deals'

              ,

          if(WildMatch([OPRT_TYPE],'New*') AND [UNIQUE_OPRT]= '1' AND Year(NEW_CLOSE_DATE)='2016' AND [WLP]='Won'

              ,'Won Deals'

              ,

          if(WildMatch([OPRT_TYPE],'New*') AND [UNIQUE_OPRT]= '1' AND Year(NEW_CLOSE_DATE)='2016' AND (COMMIT_ML_OTHER='Commit' OR COMMIT_ML_OTHER='Most Likely' OR COMMIT_ML_OTHER='Early')

              ,'Pipeline (Today)'

              ,

          if(WildMatch([OPRT_TYPE],'New*') AND [UNIQUE_OPRT]= '1'

              ,'New Deals to Pipeline'

            )))) as WaterfallIndicator

          • Re: Creating new dimension in load script
            Rupam Das

            Maybe simple one

             

            if(WildMatch([OPRT_TYPE],'New*') AND [UNIQUE_OPRT]= '1' ,if(Year(NEW_CLOSE_DATE)='2016' AND [WLP]='Won'  ,'Won Deals',

            if(Year(NEW_CLOSE_DATE)='2016' AND [WLP]='Lost' ,'Lost Deals' ,

            if(Year(NEW_CLOSE_DATE)='2016' AND (COMMIT_ML_OTHER='Commit' OR COMMIT_ML_OTHER='Most Likely' OR COMMIT_ML_OTHER='Early'),'Pipeline (Today)' ,'New Deals to Pipeline' )))) as WaterfallIndicator,