Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
pbran
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:

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

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

Can you try this

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

 

View solution in original post

9 Replies

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

Can you try this

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

 

View solution in original post

pbran
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

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?

pbran
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

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

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?

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

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

Highlighted
pbran
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?

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)