4 Replies Latest reply: Apr 11, 2012 7:11 AM by Stefan Wühl RSS

    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

        • how to get max of a count in qlikview
          Stefan Wühl

          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

            • how to get max of a count in qlikview

              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

                • how to get max of a count in qlikview

                  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.

                    • how to get max of a count in qlikview
                      Stefan Wühl

                      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