Creating new dimension in load script

[#1]

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.

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

[Anonymous · Accepted solution]

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

[rupamjyotidas]

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,