We have the following nested IF statement in HTML code but the result is not what we expect.

<p><i><span style="font-size:13px;color:blue">This overview shows budget <b>excluding</b> <%=if(Dateformat(CurrentDate(),"YYYY")="2018","18,6%",if(Dateformat(CurrentDate(),"YYYY")="2019","25,5%",if(Dateformat(CurrentDate(),"YYYY")="2020","20,3%","TBA%"))) %> burden costs. Burden Cost total is mentioned under tab Management Fee.</i></p>

This result in the following sentence where 18,6% will be automatically updated forced by the selected period (now 2018 is selected): This overview shows budget excluding 18,6% burden costs. Burden Cost total is mentioned under tab Management Fee.

For 2018, 2019 and 2020 this works well but when we select the period 2021, we still see 20,3 instead of TBA%

Does anybody know the correct HTML string or solution to keep this working for 2018 until 2020 and show TBA% for 2021?

The good news first,  I don't see a problem with the code.  To be sure, I tested your code in my system.

In a table formula, I get TBA% for the value in the IF statement when I'm in 2021  I get 20.3 when I'm in 2020.

In a report html, I get TBA% for the value in the IF statement when I'm in 2021.  I get 20.3 when I'm in 2020.

So, your code structure has no obvious flaws.

---

The bad news is … i have no earthly idea why this wouldn't work on your side.  In general when stuff like that happens, I break down the IF statement into components to see what's going on -- if you do the same, it might lead you to what's the trouble...

In this case, I would get output of each individual element... the CurrentDate(), the Dateformat(), and I might even add in GetInfo("project.fyMonthIndex") to make sure that if I'm in January, I get 0 and not 5 or something otherwise unexpected.

If everything comes back solid, then perhaps there's something more nefarious going on beneath the surface.  I am curious how other folks approach this problem...

E

Thank you very much for testing @Erik Brown, I always appreciate the help in this forum.

When I use the following simple code in a table formula I receive the below results what makes me nervous as this is not expected for the year 2021:

=if(Dateformat(CurrentDate(),"YYYY")="2020","2020",if(Dateformat(CurrentDate(),"YYYY")="2021","2021","N/A"))

Results: 2018 returns N/A, 2019 returns N/A; 2020 returns 2020; and 2021 returns 2020

The Time and calculation setting in our project are set well so I have to ask Apptio support to look into our project as it seems something in the 'engine'. When the underlying cause is known and remedial actions are implemented I will post the solution below.

That result is completely unexpected, but also explains the trouble!  I am curious what the engineering/support team brings back.  I'm glad that my simpleton's method of troubleshooting has been useful to you.  Best of luck!

Have you tried reversing the final IF statement.

if(Dateformat(CurrentDate(),"YYYY")="2018","18,6%",if(Dateformat(CurrentDate(),"YYYY")="2019","25,5%",if(Dateformat(CurrentDate(),"YYYY")!="2020","TBA%","20,3%")))

Logic says, if not 2018 and not 2019 and not 2020 then "TBA%" or if not 2018 and not 2019 and is 2020 then "20,3%"

Thank you @Anthony Robinson for your reply. I tried you option but it didn't fix the issue. See also the previous comment and my additional test. I will follow-up with Apptio support.

What I recommend is that you put a table with your burden rates and do a look up to it. That way you can adjust the table vs adjusting an HTML which people will forget to do.

Richard Coronado | Sony Picture Entertainment

Manager | Technology Business Management (TBM)

Office: 310.244.5907 | Mobile: 310.367.4785

10202 Washington Blvd, Culver City, CA 90232 USA

Ask, Answer, and Act with Apptio CT!

https://sony.apptio.com

For more information about Technology Business Management (TBM) at Sony click here<https://sony.sharepoint.com/sites/gss-tbm/SitePages/Home.aspx>.

Yes, @Richard Coronado. For those multi-year reports it's often better to store data in a table. We've seen this before in other posts.

Thank you @Richard Coronado, your suggestion is also a good and actually a better idea which works for all reports where you have the same information on top of the report. It will reduce your work because you only have to update one table instead of the html in every report. I will implement this idea but have to fix the issue with the code and the year 2021 as well. See the issue I found and explained in my reply above to @Erik Brown.

Solution: replace YYYY by yyyy in the original HTML code which was already correct.

Obviously too late to help for this question but maybe this helps for the future: 

In Unicode 'YYYY' actually refers to the date in week periods, rather than the actual day and month you are in. it returns the day as Week/Year. So, for very specific dates in certain years, you could actually be in the first week of a new year in terms of ISO 8601 weeks, but not normal day/month/year format.

When you use 'yyyy' systems then use day/month/year rather than week/year so it'll always behave as you'd expect

Tom Scott does a great video explaining it here: Why 2020 Started On December 30th - YouTube 

Yes, the weeks algorithm is extremely tricky. At some point I was generating weekly reports (not in Apptio) and every year I would have to change the logic, for it would count the weeks "wrong".

Eventually I found out that it depends on what day of the week the 1st of January falls on: if it is a Thursday or later, it will count as week 52 of the previous year. If it is before, it'll be counted as week #1 of the following one.

Leap years always have 53 weeks.

I'm writing this out of memory, so I might have fumbled something. Good ol' Google might correct me if I'm wrong.