Hello!

My team is trying to create the following table report in Apptio CT:

The rows and the hours come from our Time Tracking report, but I need help creating all of the columns shown:

PY YTD Actual Hours

A column for each current year quarter

YTD Actual Hours

The percent columns

The 6 and 12 month trend columns

Could someone help me through the steps to create these metrics in Studio v12, please?

Thank you!

@Nancy Pohuly Andrew Mackenzie

Hi Molly,

I assume that your time tracking data has been modeled. Thus, you can create a new metric for the actual hours and the percentage coloumns you need.

1. Make a calculated metric by clicking on new in the top menu ribbon and choosing metric. 

2. Make the formula in that metric = the actual hours from the coloumn in your time tracking dataset.

3. You will need to make a metric for the percentages as well or you can make a formula coloumn. You would take the the actual hours for the attribute and divide it by the sum of the actual hours for all attributes. ensure your number format is a percentage and it should work.

4. Ad that metrics to a table along with the attributes you want it broken up by.

5. You can then add a time dimension to the table by clicking on time in the explorer on the far left of your screen. It should be the last option.

6. You will want to Choose current month, YTD and current quarter to get the values you are looking for. The system will automatically calculate them for you. Drag those time dimensions into the ad hoc query configuration as columns or rows depending how you want it displayed. You can also drag a time period in, right click on it and create a range for it if you want to see multiple months or quarters side by side.

I hope that addresses everything you are looking for. I can add more screenshots if things are not clear. Please ask questions!

Thanks,

Lauren Griessel

Hi Lauren,

So sorry for how delayed this is!  That is very helpful, but now we're stuck on the last two columns.  Ideally, those columns would be =(Current Month - 6 Months Ago)/(6 Months Ago) and the same for 12 months.  i.e. if we were working with August data it would be =(August 2017 - March 2017) / (March 2017) 

How would you create a metric for that?  I've tried quite a few different ways and haven't found the right equation yet.

Thanks!

HI Molly,

Let me brainstorm this one. I don't know if it is possible. I know you can use the Currendate function for the current months cost but now I am trying to think how you can prompt apptio to look at 6 months prior in a dynamic fashion. The formula could get a little complicated.

Stay tuned,

Lauren Griessel

Hi Molly

You can use TimePeriod function. The formula should be:

=(Time_Tracking_Metric - TimePeriod(Time_Tracking_Metric,-6))/TimePeriod(Time_Tracking_Metric,-6)

Similarly for 12 months:

=(Time_Tracking_Metric - TimePeriod(Time_Tracking_Metric,-12))/TimePeriod(Time_Tracking_Metric,-12)

Regards, Ivan

Thanks, Ivan!  This seems to be working!