how to find the top 3 comments in a field by date

[tinkerz1]

Hi,

I am trying to find the top 3 Reason fields to put into a straight table by date and only if product is not blank; and also a further selection if the TYPE is A,B or C

So the table would look like

Date:                   Top 3 Reasons                  Rank             

01/01/2007         Comment A                       1                   

01/01/2007         Comment B                       2                    

01/02/2007         Comment C                       3                        

01/02/2007         Comment B                       2                   

01/02/2007         Comment A                       3                    

01/03/2007         Comment B                       1                   

01/03/2007         Comment D                       2                    

01/03/2007         Comment E                       3                  

=AGGR(IF(RANK(COUNT({1<[TYPE]={'A','B','C'},[Product]={'=LEN([Reasons])>0'}>}[Product]))=2,Count([Product])),Month,[Product])

But I can't seem to return the text part of product, I only want to see the top 3 comments for each month.

How do I do this in Set Analysis?

[eromiyasimon]

Hi

Can u specify what the dimensions you are used?

if you didn't add the product as your dimension pls try it. let me  know what you got the result

can you pls the provide the sample data?

Regards

Ero

[tinkerz1]

Ok thanks I should have added it as a dimension and not an expression

[Chanty4u]

try

am not sure

=FirstSortedValue(Top3Resons,-Date,3)

[tinkerz1]

This my formula:

=AGGR(IF(RANK(COUNT({1<[OverallOutcome]={'Upheld'},[RootCauseComplaint]-={'*'}>} [RootCauseComplaint]),4)<4,[RootCauseComplaint]),Resolved_Month,[RootCauseComplaint])

But the set analysis is returning more than 'Upheld', this formula is in a dimension in straight table.

Why is my set not selecting upheld only?

[Anil_Babu_Samineni]

From this what values you are expecting for UI

[eromiyasimon]

Hi,

You need only upheld values right. you have used a - sign in [rootcausecomplaint] which mean as of my knowledge it exclude the [overalloutcome] values which matches in [rootcausecomplaint ]

Try this:

=AGGR(IF(RANK(COUNT({1<[OverallOutcome]={'Upheld'},[RootCauseComplaint]={'*'}>} [RootCauseComplaint]),4)<4,[RootCauseComplaint]),Resolved_Month,[RootCauseComplaint])

Regards

Ero

[tinkerz1]

Hi Eromiya,

I understood [RootCauseComplaint]-={'*'} to drop all null values in root cause complaints.

If I remove the statement from set analysis, I get other data items in Overall Outcome, I just want to see 'Upheld'

[eromiyasimon]

Hi,

i think there is no need of mention rootcausecomplaint inside the set analysis

then try dis:

=AGGR(IF(RANK(COUNT({1<[OverallOutcome]={'Upheld'}>} [RootCauseComplaint]),4)<4,[RootCauseComplaint]),Resolved_Month,[RootCauseComplaint])

Regards

Ero

[tinkerz1]

I need to filter out blank entries.

My issue at the moment is selecting a set that is just Upheld cases and ranking just them.

If the set includes other cases then when calculating the top 3, their could be a case that is not 'upheld'.

Then I need to figure out how to do this by month.