Forum Discussion

Rotemshan's avatar
Rotemshan
Icon for Helper I rankHelper I
4 years ago

Ignore subtraction when there is no value (null)

Hi, 

 

I did a short formula to calculate the trend with customers, 

for that, I have the last score and the current score. 

I did a subtraction between the last score and the current score.

If the answer is positive - the trend is down (Last score is 10, Current score is 9 ==> 10-9 = 1)

If the answer negative - the trend is up(Last score is 8, Current score is 9 ==> 8-9 = -1)

I'm using it in a conditional formatting 

My problem is when there is "null" in the "Last score". the formula considers this as a 0 instead of ignoring it. 

How can I make the formula ignore when there is a null in the "Last score"? 

 

** The score column is defined as int

 

 

Need Help

6 Replies

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    HI Rotemshan 

     

    You can create an IF ISBLANK then BLANK() else...

     

    Calculated Column =

    IF ( 
         ISBLANK ( Table[Last NPS Score] ) , BLANK() , 
         SUM ( 'nps'[Last NPS Score] ) - SUM ( 'Response_Details'[Likely to Recommend NPS.1] ) 
        )

     

    Hope this helps 🙂

    Theo

  • Rotemshan's avatar
    Rotemshan
    Icon for Helper I rankHelper I
    4 years ago

    I think I got what you have tried to say

    I applied it this way: 

    Last NPS Minus NPS =
    if( ISBLANK (SUM('nps'[Last NPS Score]) - SUM('Response_Details'[Likely to Recommend NPS.1])), BLANK(),
    (SUM('nps'[Last NPS Score]) - SUM('Response_Details'[Likely to Recommend NPS.1])))
     

    Is this correct? 

     

    After I use the formula, I still have a problem with the conditional formatting as shown in the screenshot below, 
    the third guy provided the score only the second time, but it looks like the formula considers the trend as positive instead of ignoring it (and put blank)

    In a matter of fact, I'm expecting user 3 to be blank 

    and user 4 to have "Amber" Icon

     

     

     

     

    • TheoC's avatar
      TheoC
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Rotemshan I've taken your formula and adjusted slightly below:

       

      Last NPS Minus NPS = 

      VAR _1 = ISBLANK ( 'nps'[Last NPS Score] )
      VAR _2 = SUM ('nps'[Last NPS Score] )
      VAR _3 = SUM ( 'Response_Details'[Likely to Recommend NPS.1] )
      RETURN
      IF ( _1 , BLANK() , _2 - _3 )

      Hopefully this is what you're after?

      Theo 🙂

       

      • Rotemshan's avatar
        Rotemshan
        Icon for Helper I rankHelper I
        4 years ago

        Sounds good ..

        How do I apply conditional formatting based on that? 

        What are the possible values of Last NPS Minus NPS?