Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Nested If Statement

Hi,   I'm trying to achieve the following outcome in Bi using nested If Statements.   If a items status is invalid I want to do a datediff formula (This part works)  If the status is invalid and...
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Anonymous ,

    I'd like to suggest you use if statement version, it looks simpler than switch function:

    Formula =
IF (
    JEB_GAS[pdf gas cert status (groups)] = "Invalid",
    IF (
        JEB_GAS[pdf cert date].[Date] = "",
        "0",
        DATEDIFF ( JEB_GAS[pdf cert date], JEB_GAS[index run date].[Date], DAY )
    ),
    "0"
)

    Regards,

    Xiaoxin Sheng

sturlaws's avatar
sturlaws
Resident Rockstar
6 years ago

sorry, I need more information in order to help you. Can you share a screen shot of where you get your blanks? Or even better share a copy of your report?(only if it doesn't contain sensitive data. If it does, consider making a relevant samle report)

 

Cheers,
Sturla

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi sturlaws 

 

I've used your formula which has helped me massively, Please see below.

When I go into the data view when I select the drop down to see all the results in the new column I can select blanks. 

 

I would like those blanks to be zeros but i'm not quite sure why they are showing up as they are part of the Invalid group

 

 

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    my bad, change the order of the statements to this:

    a =
SWITCH (
    TRUE (),
    JEB_GAS[pdf gas cert status (groups)] = "Invalid"
        && ISBLANK ( JEB_GAS[pdf cert date] ), 0,
    JEB_GAS[pdf gas cert status (groups)] = "Invalid", DATEDIFF ( JEB_GAS[pdf cert date], JEB_GAS[index run date], DAY ),
    JEB_GAS[pdf gas cert status (groups)] <> "Invalid", 0,
    0
)

    In the Switch-statement, the statements are evaluated from top to bottom, and ends when a statement evaluates to true. Previously, when it the first statement evaluated to true when [pdf gas cert status(groups)]="Invalid", it calculated and returned datediff even if JEB_GAS[pdf cert date] is blank. Now the order is reversed, so it should work. However, you will still get blanks if JEB_GAS[index run date] is blank.

     

    -s