Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

get value by Category using STARTOFYEAR

Dear, I'm trying to calculate the annual profitability of IndexKey 2, using the most recent value and the first value of the year. But the variable StartValue is ignoring the filter (FatIndexHistori...
Need Help
Cmcmahan's avatar
Cmcmahan
Icon for Resident Rockstar rankResident Rockstar
7 years ago

Ah, this is tricky but I figured it out.

 

You don't have a value for Jan 1, 2019 for index =2.  STARTOFYEAR gets a date, and returns Jan 1 with the same year.

 

You're trying to check what the [Value] is when [Date]=1/1/2019, which is null.

Instead of using STARTOFYEAR, use FIRSTDATE and another filter constraint to make sure the year is the same.

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Cmcmahan wrote:

Ah, this is tricky but I figured it out.

 

You don't have a value for Jan 1, 2019 for index =2.  STARTOFYEAR gets a date, and returns Jan 1 with the same year.

 

You're trying to check what the [Value] is when [Date]=1/1/2019, which is null.

Instead of using STARTOFYEAR, use FIRSTDATE and another filter constraint to make sure the year is the same.

Cmcmahan ,
Well noted, I thought the function returned the first value of the year and not the first date. I'll try to resolve using FIRSTDATE.

Thank you for aswering.