topic Re: Rating changes in QlikView

Rating changes

venkatg6759 — Wed, 21 May 2014 15:28:15 GMT

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.

Re: Rating changes

arthur_dom — Wed, 21 May 2014 15:31:07 GMT

The NA Tickers should be never visualised?

Re: Rating changes

Gysbert_Wassenaar — Wed, 21 May 2014 15:32:34 GMT

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

Re: Rating changes

venkatg6759 — Wed, 21 May 2014 15:36:27 GMT

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

Re: Rating changes

venkatg6759 — Wed, 21 May 2014 15:43:33 GMT

Tried but no luck .

Re: Rating changes

SunilChauhan — Wed, 21 May 2014 15:52:35 GMT

in dimesion Ticker

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

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

Re: Rating changes

Gysbert_Wassenaar — Wed, 21 May 2014 15:53:08 GMT

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$);