Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Accepted Solutions
.png "Former Employee"){kind=icon}
Hi David,
Your sorting expression is not formed correctly. You are trying to compare individual row values (just the field name) to each other in a table that is aggregating Quantity On Hand, Reorder Point and Build point by the [Item.Sales Description].
Instead, you need to compare calculated aggregations to each other where they are calculated using the same expression that you used in your table.
I mocked up some data and used a simple SUM statement to aggregate the Quantity on Hand, Reorder Point and Rebuild point.
My sort expression is:
If(sum(total<[Item.Sales description] > [Item.Reorder Point]) <=sum(total<[Item.Sales description] > [Item.Quantity On Hand])
,1
,If(sum(total<[Item.Sales description]> [Item.Build Point]) <=sum(total<[Item.Sales description]> [Item.Quantity On Hand])
,2
//,If(IsNull([Item.Reorder Point] or [Item.Build Point])
,3
//)
)
)
I applied the expression to the [Item.Sales description] in the Sort tab in the properties panel. I don’t think the IsNull test to assign 3 is necessary. If I'm mistaken then you will need to add it back into your expression. Be aware that when aggregating fields with null's QlikView may return zero (0).
The TOTAL keyword is used to total values across dimensions. When there are modify brackets ‘<>’ then the TOTAL keyword effectively subtotals by the field inside the angle brackets, [Item.Sales description], in this case. To learn more about the "total" keyword check the online QlikView documentation at help.qlik.com or search Qlik Community - there are several postings explaining this keyword.
Properties Panel:
Resulting Table:
If you’re still having problems to get your table to sort correctly, then post a sample of the data and the expressions you are using to calculate the reorder point, build point and quantity on hand.
Regards,
Jeff
Hello,
Thank you for the information. I've been playing around with the expression but am still having some issues.
I am still seeing items with null build and reorder points. I tried adding my IsNull expression back in with no result. That expression should have been and, not or, if that makes any difference.
For the Reorder and Build points, each item will have one or the other. Reorder applies to Inventory Parts and Build Point applies to Inventory Assemblies. This particular dashboard is for our purchasing dept. and the Inventory parts will make up the majority of her workload. We only have one assembly type (Screenprinted bottles) that is built by another company, so I do need to include those as they are important to track. The other assemblies are taken care of by our production dept. and I think I have that dashboard dialed in.
The reorder point (and Build point for screenprinted bottles) act as par levels that Purchasing will set up within Quickbooks. I would like to see all of the inventory parts (with reorder, not build, points) that have reached or dropped below their par levels (Reorder points) at the top of the table. I would like to follow this with the Screenprinted bottles that have reached or dropped below their par levels(Build Points). Everything else should come after as they don't need immediate attention.
Actually, I would like to exclude any build assemblies other than the SP Bottles and exclude some other things as well. I did get some help on that issue using len and mid functions which seemed to work for me. They aren't shown in the below screen captures because I was playing around with everything trying to get the sort to work.
I will post the captures below for you to look at my work. Let me know if you need other info or if their is a way to get a file to you to check out.
Thank you for all of your help. Trying to turn QB into a Materials Requirement Planning tool. 🙂
Hi,
I added a combination of nulls to my data and eliminated items using a calculated dimension. You should only need to do this for one of the dimensions then check the “Suppress When Null Value” checkbox.
The expression I used to calculate the dimension:
=if(len([Item.Build Point]) and len([Item.Reorder Point])
,[Item.Sales description]
)
Qlik evaluates numbers as Boolean where 0=False and others evaluate as True. Since a NULL value returns a zero length it evaluates as False and a NULL is returned for the dimensional result. By checking “Suppress When Null Value”, rows with a NULL dimensional value are eliminated from the chart/table.
Before: Null/Zero rows included
After: Null/Zero rows excluded
To eliminate specific items, you can use Set Analysis. Set Analysis is a way to only include certain rows of data in the calculation or exclude certain rows from the calculation. I would suggest looking at the various materials available to learn more about it. Various resources (to name a few) include: Qlik Community, on-line documentation (help.qlik.com), youTube videos plus others.
To exclude a specific item from the table I used set analysis to drop the ‘R widget’ item. The set analysis for this is:
{$<[Item.Sales description]-={'R widget'}>}
and is added to the expression
sum({$<[Item.Sales description]-={'R widget'}>} [Item.Build Point])
With all of the expressions being updated to include the set analysis statement, the ‘R widget’ row is gone.
The set statement needs to be added to every expression in the table for the row to be dropped.
Be careful with calculate dimensions as they can impact performance. In general, set analysis tends to be more efficient.
Here’s an example of only including ‘R widget’ (no minus sign in front of the ‘=’ sign)
sum({$<[Item.Sales description]={'R widget'}>} [Item.Build Point])
Now, only the ‘R widget’ row is included.
This should get you closer.
Regards,
Jeff
