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

# Countifs equivalent

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

CountryFruitPrice
AustraliaApple20
AustraliaApple20
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

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

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

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?

• ###### Re: Countifs equivalent

Hi Chris

I noticed also that 'Australia / Pear' upcome blank. Unfortunately i don't understand why. I just upload your table as an inline load. And i'am not expert enought to understand what was wrong.

regarding Modifiers and behavior depending of selections this document may help you much more better than I ever will

And for this quesiton "

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

" .

By my opinion the best way is to hard coding it directly in the table, As Qlik sense reset all selections when leaving.

regards,

Bruno

• ###### Re: Countifs equivalent

Chris Ellingworth wrote:

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 don't know why we need Aggr() here, the reason for missing count for Australia/Pear is the grain mismatch here. Aggr() is only using Country and Price as dimension where as the chart itself contains Fruit as the dimension also. To fix this, you can do this -> Aggr(NODISTINCT Count(DISTINCT Fruit), Country, Price). But the simpler and more efficient alternative would be to avoid Aggr() and use TOTAL qualifier -> Count(DISTINCT TOTAL <Country> Fruit)

Read here on the above topic: When should the Aggr() function NOT be used?

Pitfalls of the Aggr function

AGGR...

Use Aggregation Functions!

• ###### Re: Countifs equivalent

Hi Sunny

Thanks a lot for the explanation , not sure to understand why it was mismatching. I wish i could fixed details and issue as you do.

If you have a minute can you have a look to this post : ( I tried to help also but can fixed the problem, may be I should'nt respond to post even if them seemed obvious )

let me know if you want me to translate : the problem concern a cumulative sum that give wrong value or negative value

Thanks Sunny.

• ###### Re: Countifs equivalent

I will def. take a look sometime today

• ###### Re: Countifs equivalent

Thanks very much Sunny and Bruno. I am at work again tomorrow and I will try out your latest responses to me then. I appreciate this very much and am very happy to have found such great software and a forum with such helpful people. Cheers.

Chris.