Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to get max of a count in qlikview

Hi All,

I am new to Qlikview.I exported my csv file into qlikview and started creating small tables,pie charts etc.But,the problem is i am calculating counts in one report and i get to see the counts for each row.But,the problem is how do i get to calculate the max(count) so that the query just returns me a single row.?to simplify suppose i am having 5 rows then i am able to display the count of each row in a new column .But from that row i need to find the maximum value.i tried using rank(count(<column_name>)) but this does not work.PLease help me in this case.Please let me know where do i write such a query instead? thanks,Aniket

1 Solution

Accepted Solutions
swuehl
MVP
MVP

It's probably a use case for advanced aggregation. Please have a look at the Help, aggr() function and advanced aggregation. The expression might look like

=max( aggr( count([column_name]), YOURTABLEDIMENSION ))

YOURTABLEDIMENSION is the field (or fields, comma separated),that you used as dimension to group by your rows.

Hope this helps,

Stefan

View solution in original post

4 Replies
swuehl
MVP
MVP

It's probably a use case for advanced aggregation. Please have a look at the Help, aggr() function and advanced aggregation. The expression might look like

=max( aggr( count([column_name]), YOURTABLEDIMENSION ))

YOURTABLEDIMENSION is the field (or fields, comma separated),that you used as dimension to group by your rows.

Hope this helps,

Stefan

Not applicable
Author

Hi , thank you for the reply. i tried doing what you suggested.I am using the folliwng formula:max( aggr( count([url]), month([time]) )) .But this returns null rows.i am inserting the formula in the definition section and not inthe conditoinal section where am i going wrong? thanks,Aniket

Not applicable
Author

hey, i could get the max count but the problem is i am not able to see the other column values for it.How to bring the values for other columns? thanks.

swuehl
MVP
MVP

Ah, maybe I misunderstood your requirement. Above expression is useful e.g. if you want to get the max of the count in a text box.

I think you want to do something like a Top N display, showing only the top N (N=1 in this case, you only want the max count) dimension values, ranked after the results of an expression?

Are you using QV11? You could then use dimension limits for that. Use your count as first expression in your table, then enter dimension limits tab and check the 'Restrict ...' check box and select option Show only 'Largest 1 values'

If you are not on QV11, you can do similar by sorting your table by y-value descending, then limit max values shown to 1 in presentation tab.

Hope this helps,

Stefan