top 10 values for the highest value of year

[#1]

Hi,

I have a date field,

I want to pick out the highest value of year from the dates and I want to display the top 10 dates in that year having the highest count for a particular field..

I gave this in the dimension in a straight table,

=IF(DATE(SUBFIELD(RPX_ACTIVATION_DATE,' ',1),'YYYY')='2013',RPX_ACTIVATION_DATE,NULL())

2nd dimension ->RPX_ACTIVATION_DATE

and in the expression,

I gave count(MRP_ID_SDP)..

but, the straight table is not being populated ... the dimension field is null...

How do I get the desired result?

Thanks,

Anju

[Clever_Anjos]

1) I would create a field using function YEAR(RPX_ACTIVATION_DATE) at script level. Using functions to create a dimention is not a great idea

2) Using "Dimention Limits" you can easily show only 10 highest values

[#3]

I tried that ....

I want the top 10 count(MRP_ID_SDP) for the latest year ... say 2013 ..... if I give dimension limits ... it sorts year wise

[yevgeniy]

Hi,

You need function: rank.

if you need 10 date  max sales

Sample:

You need add dimension: your dimention

next step

Add Calculated Dimension:

=Aggr(if(rank(sum([Sales]),4,1) <= 10,[Your Date],Null()),[ your dimension ],[Your Date])

next step:

Suppress when value is null

next step:

add expression sum(Sales)

Regards,

Yevgeniy

[Clever_Anjos]

tryc

ount({<YEAR={"=$(=MAX(your_year_field))"}>}MRP_ID_SDP)

[#6]

Hi!

This did not work for me!

-Anju

[#7]

this did not work!

-Anju

[#8 · Accepted solution]

Hi,

I was able to find a solution here.

http://community.qlik.com/message/182544#182544

-Anju

[#9]

Can you atach an example?

[#10]

Hi,

PFA the sample doc.

Regards,

Anju