Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pbran
Contributor II
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:

DepartmentCount of ProductsPercent Covered
Biological3485889.40%
Mechanical5011992.05%
Admin/HQ56098.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 NumberInventoryRequirementPercent Covered
44439111211399.11%
4445698635024.57%
4435511211109.09%
44311246360077.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)
1 Solution

Accepted Solutions
sunny_talwar

Can you try this

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

 

View solution in original post

9 Replies
sunny_talwar

Can you try this

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

 

pbran
Contributor II
Contributor II
Author

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

 

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

sunny_talwar

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?

pbran
Contributor II
Contributor II
Author

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

Screen Shot 2019-09-30 at 4.03.51 PM.png

sunny_talwar

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

pbran
Contributor II
Contributor II
Author

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]"}>}

sunny_talwar

pbran
Contributor II
Contributor II
Author

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?

sunny_talwar

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)