Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Can you try this
Count({<ProductNumber = {"=[Inventory] >= [Requirement]"}>} ProductNumber)
/
Count(ProductNumber)
Can you try this
Count({<ProductNumber = {"=[Inventory] >= [Requirement]"}>} ProductNumber)
/
Count(ProductNumber)
Thank you for the reply! Unfortunately that does not work. Message:
Error in set modifier ad hoc element list ',' or ')' expected
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?
Sure thing! The error message has changed now. Not sure why
Seems like you don't have a field name called ProductNumber? Is it called something else may be like [Product Number] or productnumber?
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]"}>}
Try this A Primer on Set Analysis
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?
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)