Countifs equivalent

[#1]

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

Country	Fruit	Price
Australia	Apple	20
Australia	Apple	20
Canada	Apple	20
Australia	Pear	20
Australia	Apple	25
USA	Apple	20
Lichtenstein	Pepperoni	116.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.

[sunny_talwar · Accepted solution]

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)

[brunobertels]

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

[#4]

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?

[brunobertels]

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

https://community.qlik.com/servlet/JiveServlet/download/1030072-223741/Les%20set%20analysis_ENG.pdf

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

[sunny_talwar]

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?‌

Other useful links on Aggr:

Pitfalls of the Aggr function

AGGR...

Use Aggregation Functions!

[brunobertels]

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 )

https://community.qlik.com/thread/216511?sr=inbox&ru=220521

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

Thanks Sunny.

[sunny_talwar]

I will def. take a look sometime today

[#9]

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.