Nested IF

[Samanehsorournejad]

Hi Every body,

I want try to write the nested if with this Conditions:

if just column [DWH:Pentaho Abbruch]=1 then 'Pentaho' else if column [DWH:Qlikview Abbruch]=1 then 'Qlikeview'

else if both of them are equal 1 then 'Pentaho and Qlik View'. I had write this formula but I could not understand what is my Fault. it gives me Error.

If( [DWH:
Pentaho Abbruch]=1, 'Pentaho',if([DWH:
Qlikview Abbruch]=1,'Qlikeview',if([DWH:
Pentaho Abbruch]=1 and [RZV:
Qlikview Abbruch]=1, 'Pentaho and Qlik View')))

Please help me 😞

[vinieme12 · Accepted solution]

You logical order was incorrect, the if() will end after first condition that is true 

Try below

=if(rangesum([DWH:Pentaho Abbruch],[RZV:Qlikview Abbruch])=2,'Pentaho and Qlik View'

,if([DWH:Pentaho Abbruch]=1,'Pentaho'

,if([DWH:Qlikview  Abbruch]=1,'Qlikeview','None of Above')))

[Samanehsorournejad]

Hi Vineeth,

it did not unfortunately works. It shows for all cells   'None of Above'

[vinieme12]

are  the values actually numeric??

what does isnum([DWH:Pentaho Abbruch]). return ?  0 means not numeric. , -1 means numeric

[vinieme12]

incase they are not numeric try below

=if(index([DWH:Pentaho Abbruch],'1') and index([RZV:Qlikview Abbruch],'1') ,'Pentaho and Qlik View'

,if(index([DWH:Pentaho Abbruch],'1'),'Pentaho'

,if(index([DWH:Qlikview  Abbruch],'1'),'Qlikeview','None of Above')))

[Samanehsorournejad]

Hi Vineeth,

isnum([DWH:Pentaho Abbruch])  return 0 for all rows execp the first rows that returns -1 and I had try to this:

=if(index([DWH:Pentaho Abbruch],'1') and index([RZV:Qlikview Abbruch],'1') ,'Pentaho and Qlik View'

,if(index([DWH:Pentaho Abbruch],'1'),'Pentaho'

,if(index([DWH:Qlikview Abbruch],'1'),'Qlikeview','None of Above')))

but again give me the error.

[vinieme12]

What is the error that you get ? Please post a screenshot or some sample data

[Samanehsorournejad]

Hi Vineeth,

I had change the type of Columns to Integer and tried this Formula:

=if(RangeSum([DWH:
Pentaho Abbruch],[DWH:
Qlikview Abbruch])=2,'Qlikview and Pentaho',if([DWH:
Qlikview Abbruch]=1,'Qlikview', if([DWH:
Pentaho Abbruch]=1,'Pentaho','Non of Abov')))

the Error just write :  Error in expression

and in my table for all case it just show "Non of Abov"

[vinieme12]

I think you are trying to add this as a dimension and not a measure

For a calculated dimension, you need to wrap this in Aggr()

=

Aggr(if(index([DWH:Pentaho Abbruch],'1') and index([RZV:Qlikview Abbruch],'1') ,'Pentaho and Qlik View'

,if(index([DWH:Pentaho Abbruch],'1'),'Pentaho'

,if(index([DWH:Qlikview Abbruch],'1'),'Qlikeview','None of Above')))

,SomeIdOrKeyField)

SomeIdOrKeyField, this field needs to be at lowest granularity, that is row level

[Samanehsorournejad]

Hi Vineeth ,

I had change the type of Columns to Integer and write the Formula in the Order that you at first told me and its Works,

=if(rangesum([DWH:Pentaho Abbruch],[RZV:Qlikview Abbruch])=2,'Pentaho and Qlik View'

,if([DWH:Pentaho Abbruch]=1,'Pentaho'

,if([DWH:Qlikview Abbruch]=1,'Qlikeview','None of Above')))

my Fault were:

1- Orders of IF Condition

2- Type of Column that should be Change in Integer

🙂

Thanks a lot Vineeth