6 Replies Latest reply on Aug 2, 2016 2:47 PM by Yan Gonzalez

# How can I display only the last record of a calculated field?

Hello guys! Such a pleasure to be here! Yan Gonzalez, from Paraguay here. Well, I'm working on this report, calculating the current yield (CY) on traded bonds at my local stock exchange (where I work). The CY is a calculated field which extracts info from 2 .xls: One withe the Operation date among other stuff, and another with the bond's rating, among other stuff too.

Thing is, once I have the CY, I want to take the value from the last operation only, so I can have a yield curve for each bond rating. I tried a few things, like the Last() filter, but with no results. I'm really a newbie to this kind of filtering, so any help would be more than welcome!

P.S.: The attached workbook is a small sample, the full tables have more than 6000 operations and 90 companies.

• ###### 1. Re: How can I display only the last record of a calculated field?

Yan,

I started looking into it, but was in need of some clarifications.

To be the last operation, is that the same as having the highest [N° de Op]?

If so, you can use a Level of Detail calculation to fix the max for

all points for use in later comparison [MaxOpNumber]:

{ FIXED : MAX([N° de Op])}

Then to filter to get a curve for each bond rating,

which variable in your set corresponds to the bond rating?

In the attached example, I just used ISIN and said,

find the ISIN that has the highest OpNumber and

just filter to that.

[Isin]=[ISINMaxFix]

[ISINMaxFix]

{ FIXED :MAX([ISINMaxOp])}

[ISINMaxOp]

IF [N° de Op]=[MaxOpNumber]

THEN [Isin]

END

• ###### 2. Re: How can I display only the last record of a calculated field?

Hello swaroop! Thanks for taking the time to check my work. Yes indeed, the highest [N° de Op] is the last operation. I'm still not familiar with LOD calculations, so this is an excellent opportunity to learn. Any links will be greatly appreciated. What does { FIXED : } actually do?

The variable for the bonds' ratings is  [Calificacion]

Thanks again!

• ###### 3. Re: How can I display only the last record of a calculated field?

Yan,

Thank you for the information.

In need of a few more clarifications.

The max [N° de Op] seems to only be associated with [Calificacion] of "pyAAA".

It is not clear how to get a curve for each bond rating if the max Operacion is only associated with one rating.

The attached finds that [Calificacion] for the max [N° de Op], and then filters the graph to just points of pyAAA.

It may be helpful to mock up a drawing of what your desired graph should look like.

"FIXED level of detail expressions compute a value using the specified dimensions, without reference to the dimensions in the view."

Overview: Level of Detail Expressions

Level of Detail (LOD) Expressions | Drawing with Numbers

• ###### 4. Re: How can I display only the last record of a calculated field?

The idea is to have a max [N° de Op] for each bond (ISIN).

This:

[ISINMaxFix]

{ FIXED :MAX([ISINMaxOp])}

Will output only one value?

The graph that I want to have will have [Tasa de Interes] (rate) on the y axis and [Plazo años] (years) on the x axis. For each ISIN I need the max [N° de Op], and finally it should be able to be filtered by [Calificacion] and [Emisores].

• ###### 5. Re: How can I display only the last record of a calculated field?

Yan,

Sorry for all the back and forth.

Please see Sheet 5 of the attached.

To get the max for each ISIN, then you'll need to use:

{ FIXED [Isin]:MAX([N° de Op])}

Then as a filter

[N° de Op]=[ISINMaxFix]

which should only return the max [N° de Op] for each ISIN.

1 of 1 people found this helpful
• ###### 6. Re: How can I display only the last record of a calculated field?

Thank you swaroop! You don't have idea how much you helped me!

Now I need to look in detail to this LOD business!