Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
tinkerz1
Creator II
Creator II

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

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?

10 Replies
eromiyasimon
Creator II
Creator II

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
Creator II
Creator II
Author

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

Chanty4u
Champion III
Champion III

try

am not sure

=FirstSortedValue(Top3Resons,-Date,3)

tinkerz1
Creator II
Creator II
Author

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
eromiyasimon
Creator II
Creator II

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
Creator II
Creator II
Author

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
Creator II
Creator II

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
Creator II
Creator II
Author

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.