Apptio for All

 View Only

 Help with IF statements in TBM Studio

Jump to  Best Answer
  • ApptioforAll
Matthew Sparks's profile image
Matthew Sparks posted Tue November 02, 2021 03:11 PM
Hi all, 

I am needing to add a few columns to a report. The blank columns below need to be calculated based off other columns in the report. For example, what we are wanting to do for the Over Under calculated column if this table were in EXCEL =IF("Over / Under column"<0, "-", "+"). In other words if the value in the Over/Under column cell is less than 0 we want the resulting cell in Over Under to represent -, and if it is greater than 0 we want it to present +. 

In the xFB column we want the same thing as the formula for Over Under, so in EXCEL we have it as IF("Estimated Excessive Fund Balance column"<0, "-", "+"). So if the value in that column is less than 0, return a "-" and if it is greater than 0, return a "+". 

The final column name Review Trend, we want to compare Over Under and xFB to see if those values are the same and if they are then we want to return a Y, if not we want the cell to remain blank. 

I mainly want to see if there is a way to reference a column in the same report as we are trying to drive the formula, and how we would write those IF formulas. 

Hopefully someone can help me based off the info I provided, I tried writing these formulas but I kept getting errors. 



@Debbie Hagen, do you have any suggestions on this one?​
#ApptioforAll
Jenny Franklin's profile image
Jenny Franklin posted Tue November 02, 2021 04:25 PM  Best Answer
Apptio Community Member updated Tue November 02, 2021 04:51 PM

@Matthew Sparks, Apptio can be a tad persnickety when it comes to ​typing out formulas.  It may be seeing the "/" as a division symbol.  Might try =If({Over / Under}<0,"-","+").
#ApptioforAll
Jenny Franklin's profile image
Jenny Franklin posted Tue November 02, 2021 03:56 PM
Hi @Matthew Sparks - can you post the formula you're using in the Apptio report that's giving you an error?  ​
#ApptioforAll
Matthew Sparks's profile image
Matthew Sparks posted Tue November 02, 2021 04:08 PM
@Jenny Franklin, I have tried multiple formulas. I have just tried one that actually is only giving me my false expression result. So for the Over Under column I have =If("Over / Under" < 0, "-","+"). But it is only giving me the + result and that isn't correct for all the values. ​​​
#ApptioforAll
Matthew Sparks's profile image
Matthew Sparks posted Tue November 02, 2021 04:17 PM
@Jenny Franklin I also tried using =if((YearToDate(Revenue)-YearToDate(A3))<0, "-","+") where (YearToDate(Revenue)-YearToDate(A3) is the formula used to calculate the Over / Under column. I received an error with that one.​
#ApptioforAll
Jenny Franklin's profile image
Jenny Franklin posted Tue November 02, 2021 04:36 PM
Apptio Community Member updated Tue November 02, 2021 04:46 PM


I'm getting the same deal when using that formula - everything is "-".  What if you do something like this instead and show a trend icon?


Previous Mo is the Previous Month metric, which is =Previous Month(Cost)

Current Mo is Cost, and then I right-clicked on the header to rename it

I have a Variance Step1 column that I later hid (right-click the column name in the Values area and select Hide)

Variance Step1=Abs(Cost)-Abs(Previous Month Costs)


Variance True (later renamed column to just Variance) is:

Variance True =If(Previous Month Costs=0 AND Cost<0,Cost,{Variance Step1}


Trend is another calculated column that was created on the report itself:

Trend ="<div align='center'>"&Icon("3arrows",Variance True>0,Variance True=0,Variance True<0)&"</div>"


@Matthew Sparks​


#ApptioforAll
Matthew Sparks's profile image
Matthew Sparks posted Tue November 02, 2021 04:45 PM
@Jenny Franklin It worked for me! It is showing exactly how it was in EXCEL. Thank you so much for the Syntax corrections!
#ApptioforAll
Jenny Franklin's profile image
Jenny Franklin posted Tue November 02, 2021 04:48 PM
@Matthew Sparks awesome!!  Glad it worked for you - no idea what I'm doing on my end because it's not working for me lol!  But it's working for you and that's what matters!   🙌😎​
#ApptioforAll

Related Content