Rating changes

[venkatg6759]

Hello Everyone,

I have tickers which have few ratings .Each ticker has different ratings and changes as per rating date .

I want to exclude the tickers that are having rating NA for max rating date. I am able to achieve this. But when i select a date range the tickers which are excluded NA in between it might have different rating

Ex:

Ticker              Rating                     Rating Date 

AEO                 B                           10/22/2013

AEO                  N                            03/31/2014

AEO                  NA                          05/15/2014

I am able to remove the ticker when it is NA by taking Max date. But when i select a date range .

In this ex OCT 2013 - April 2014 . If i select

Result should be

AEO                  N                            03/31/2014

How can i achieve this . Also find the sample attachment.

[arthur_dom]

The NA Tickers should be never visualised?

[Gysbert_Wassenaar]

Try firstsortedvalue({<Rating -= {'NA'} >}Rating, -[Rating Date])

[venkatg6759]

Yup NA tickers should not be visualized even when they fall in any date range

[venkatg6759]

Tried but no luck .

[SunilChauhan]

in dimesion Ticker

only({<Rating -= {'NA'},[Rating Date]={$(=max([Rating Date]) )} >}Ticker )       

or

Count({<Rating -= {'NA'},[Rating Date]={$(=max([Rating Date] ))} >}Ticker )    

[Gysbert_Wassenaar]

Your date field doesn't contain dates, but string values. Use the date# function in the script to create dates from the string values:

dat:

LOAD Ticker,

     date#([Rating Date],'MM/DD/YYYY hh:mm:ss') as [Rating Date],

     [Author Name],

     [Rating Type],

     Rating,

     [Rating From]

FROM

C:\Users\goginv\Documents\Ratings_1.xls

(biff, embedded labels, table is Sheet1$);