Apptio for All

 View Only

 Lookup value based on the selected period/year

Jump to  Best Answer
  • ApptioforAll
Apptio Community Member's profile image
Apptio Community Member posted 10/19/22 03:50 AM
Hi all,

We have the following certificate pricing table and use the Lookup function in another table to retrieve the price of the certificates. This works well with the following formula:

=Lookup(Certificate,OBJ Digital Certificates Pricing GLB,Certificate,{2022})

but the last part {2022} is now fixed and we would like to have this replaced by something that is using the selected period so when you select a month in 2023 the formula should retrieve the price of this certificate located in column 2023.

What is the trick to make this formula dynamic?


#ApptioforAll
Jenny Franklin's profile image
Jenny Franklin posted 10/19/22 10:48 AM  Best Answer

Hi @Menno Wittebol, I pinged @Renee Daignault and she said if you add a Date Partition to the table, that would change the years to 'Collapse Value', and then you could reference 'Collapse Value' in your formula instead of the specific year.  That way regardless of what year you're in, you will see the current data for that year.

​​


#ApptioforAll
Apptio Community Member's profile image
Apptio Community Member posted 10/20/22 01:55 AM
Hi @Jenny Franklin, thank you for your reply.

I did the trick with the Unpivot function on the field Certificate which creates a new table of (in this example) 8 rows where every row contains a unique combination of the field Certificate, YYYY and Value.

Then I created a Unique Identifier column of the combination Certificate and the Collapse Column (= YYYY). The same Unique Identifier should be created in the other table (there you will use the Value) as well where the YYYY is retrieved by CurrentDate("yyyy") and will be based on the month you select.

With the Unique Identifier I am now able to retrieve the Value of a Certificate in a particular year based on the selection made by the Date Range button/selection located in the top of the Apptio screen.
#ApptioforAll

Related Content