Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Creating new dimension in load script

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

1 Solution

Accepted Solutions
okolyug16
Not applicable

Re: Creating new dimension in load script

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

2 Replies
okolyug16
Not applicable

Re: Creating new dimension in load script

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
Not applicable

Re: Creating new dimension in load script

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,