8 Replies Latest reply: Jun 13, 2016 1:02 AM by Chris Ellingworth RSS

    Countifs equivalent

      Hi, I'm used to using countifs in Excel. If I have a spreadsheet as follows:





      What is the right way to count the occurrences so that I can use it in graphs and tables etc? If I've set Country as the dimension in a table how can I get the count of Apple and 20 against the various countries in the next column? Also, what would the expression look like if just Apple was counted?




        • Re: Countifs equivalent
          Sunny Talwar

          To get the count of just Apples, you can do this:






          Count({<Fruit = {'Apple'}>} Fruit)

          To get 20 in the next column, you can put a static 20 as another expression (Not entirely sure I understand this part)

          • Re: Countifs equivalent
            bruno bertels

            Hi Chris


            Not sure to understand well your need but


            First with seta analysis your able to count distinct fruit depending of there price :


            with country as dimension you should have this :


            Or with a AGGR(() function



            Not sure that's your re looking for.


              • Re: Countifs equivalent

                Thankyou Sunny T and Bruno bertals very much for your help.


                Do I just set the action on your answers to 'Helpful' or is there something further that I should do like make a reply 'Answered'? I guess this is not in my control?


                Bruno, can you tell me what happened to the 'Australia / Pear' distinct count? It is coming up blank in your table that you posted.


                I have started playing with set object state (alternate states). It won't respond to the alternative selections filter pane unless I take out those modifiers ($,1,1-$). Does putting in a modifier lock it into the main selections and ignores alternative selections even if the table is set to the alternative selection filter pane? What is the default modifier if you don't put in $, 1 or 1-$?


                When I exit Qlik, those alternative selections are all lost. What is the best way to lock in a table to alternative selections so that two tables on the same sheet can be seen with different selections each time Qlik is started or is it just expected that these selections are hard coded with expressions if this sort of thing is always needed to be the same way?