# Qlik Sense App Development

New Contributor II

## Find Average from "count if" and "count total" in Expression

Hi everybody,

I'm trying to find the percent of inventory coverage. I have a table showing each department on the rows. My goal is to have a column that displays the percentage covered, but Qlik's expression syntax is trippy and is messing me up.

If the Inventory is equal to or greater than the Requirement for that product number, it is considered covered. The percent covered column expression should only consider an item covered if its Inventory is greater than the Requirement, otherwise it is not covered at all.

Here is an example of what the end table should look like:

 Department Count of Products Percent Covered Biological 34858 89.40% Mechanical 50119 92.05% Admin/HQ 560 98.22%

To hopefully showcase the logic I'm wanting to achieve, this is a table of the Product Number, Inventory, and Requirement of a department that has 4 products:

 Product Number Inventory Requirement Percent Covered 444391 112 113 99.11% 444569 86 350 24.57% 443551 12 11 109.09% 443112 463 600 77.17%

In this example, the percent covered column for this department would be 25%, because only one product number met the criteria for being covered (third row).

To get this logic, I've kept on an `if` statement to be `If(Inventory > Requirement, 1, 0)`so that if it's covered it's 1, and if not it's 0. All my logic has focused around this concept within an average formula, but in different syntaxs.

=Count(If([Inventory]>=[Requirement], ProductNumber)) / Count(ProductNumber) * 100

=Sum(If([Inventory]>=[Requirement], 1, 0)) / Count(ProductNumber) * 100

I get an error "Calculation Has Timed Out" or it returns all 0%s (Which isn't correct based on the data)

Does anyone have any idea how to get this expression formula to work! Thank you so much!

Labels (3)

• ### logic

1 Solution

Accepted Solutions MVP

## Re: Find Average from "count if" and "count total" in Expression

Can you try this

``````Count({<ProductNumber = {"=[Inventory] >= [Requirement]"}>} ProductNumber)
/
Count(ProductNumber)``````

9 Replies MVP

## Re: Find Average from "count if" and "count total" in Expression

Can you try this

``````Count({<ProductNumber = {"=[Inventory] >= [Requirement]"}>} ProductNumber)
/
Count(ProductNumber)``````

New Contributor II

## Re: Find Average from "count if" and "count total" in Expression

Thank you for the reply! Unfortunately that does not work. Message:

Error in set modifier ad hoc element list ',' or ')' expected MVP

## Re: Find Average from "count if" and "count total" in Expression

I don't see any issue with the expression, may be you added or removed something from it? Can you post a screenshot of the expression and the error message?

New Contributor II

## Re: Find Average from "count if" and "count total" in Expression

Sure thing! The error message has changed now. Not sure why  MVP

## Re: Find Average from "count if" and "count total" in Expression

Seems like you don't have a field name called ProductNumber? Is it called something else may be like [Product Number] or productnumber?

New Contributor II

## Re: Find Average from "count if" and "count total" in Expression

Goofy me, you're right! Long days can really let you slip. Thank you for the answer!

Bonus question: Is there Qlik documentation that explains the syntax why we're passing things as nested objects and strings? {<[field] = {"=[field] > [field]"}>} MVP

## Re: Find Average from "count if" and "count total" in Expression

New Contributor II

## Re: Find Average from "count if" and "count total" in Expression

The expression is now timing out when inserted with the real data. I'm having difficulty finding any reference to why this is in the docs; is this because it's being loaded in the expressions?

Would it give better performance if a new column was generated in the data script loader? MVP

## Re: Find Average from "count if" and "count total" in Expression

Yes, it would definitely help if you can create a flag in the script like this

``If([Inventory] >= [Requirement], 1, 0) as Flag``

and then all you need is this

``Count({<Flag = {'1'}>} ProductNumber)/Count(ProductNumber)``