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

    Countifs equivalent

    Chris Ellingworth

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

       

      CountryFruitPrice
      AustraliaApple20
      AustraliaApple20
      CanadaApple20
      AustraliaPear20
      AustraliaApple25
      USAApple20
      LichtensteinPepperoni116.35

       

       

      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?

       

      Thanks.

      Chris.

        • Re: Countifs equivalent
          Sunny Talwar

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

           

          Dimension:

          Country

           

          Expression:

          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 :

            Count({$<Fruit={'Apple'},Price={20}>}Fruit)

            with country as dimension you should have this :

             

            Or with a AGGR(() function

             

             

            Not sure that's your re looking for.

            Bruno

              • Re: Countifs equivalent
                Chris Ellingworth

                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?