Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Dpage
New Contributor III

Sort by Expression

Hello,

Another question. I am trying to sort by expression. My dimensions are Item.Full Name,  Item.Sales description, and Item.Preferred Vendor Full Name. 

The expressions calculate Qty. on Hand, 1 shows the Reorder Point and 1 shows the Build Point (depending on the Item Type) and then I have a column for the avg usage.

I created a sort by expression that sorts the first dimension Item.Full Name

If([Item.Reorder Point]<=[Item.Quantity On Hand],1,If([Item.Build Point]<=[Item.Quantity On Hand],2,If(IsNull([Item.Reorder Point] or [Item.Build Point]),3)))

The idea is to show the items that are low first (the Qty. on Hand value turns red if < or = to the reorder or build point, whichever applies) and then to show the rest with the Qty. on Hand value in green.

However, when I scroll through, it starts out perfect, all red, but then I see a whole bunch of green and red quantities interspersed throughout the table. Can't discern why. According to the table, the Qty. on Hand is clearly more than the reorder or build point and items lower down, the QOH is = or < the reorder or build point.

Let me know if you need more clarity.

Thanks!

Labels (2)
1 Solution

Accepted Solutions
Dpage
New Contributor III

Re: Sort by Expression

Hello,

In the end, I simplified the issue by creating a separate dashboard for the Item Type associated with Build Points (as opposed to Reorder Points). This allowed me to use the simple Sort expression If([Item.Build Point]>=[Item.Quantity On Hand],1,2) and If([Item.Reorder Point]>=[Item.Quantity On Hand],1,2) respectively. This worked perfectly for me and ordering the SPB items is a distinct process anyway. 

Thanks again for the advice. I think I was making the issue overly complicated in an attempt to save space.

11 Replies
Support
Support

Re: Sort by Expression

In this case, it will be best if you can attach a sample app showing the issue.  If you want to use the app you have but there is some confidential fields etc., you can scramble those fields via the Document Properties Scrambling tab, just FYI.  I believe if folks can see things in more detail, we can likely better help you with things, this may be something in the data model as well.

Regards,
Brett

To help users find verified answers, please don't forget to mark a correct resolution or answer to your problem or question as correct.
Dpage
New Contributor III

Re: Sort by Expression

Thank you for the reply. I should point out that I am working in Quickbooks Advanced Reporting which uses Qlikview software to build the reports. I have been able to solve many of my issues using the Qlik community but I am not sure that I can upload a file for you guys to work with. I could show some screenshots if that helps but I don't believe the QBAR files can be opened via the Qlikview app.

Basically I want to create a hierarchy for sorting based on three different parameters. I would like to show the higher priority items first, those that are at or below the Reorder and Build points(which are set withing Quickbooks) first than everything else.

Support
Support

Re: Sort by Expression

Aha, thanks for that info, now things make a bit more sense! 🙂  Let me see if I can get someone to have a look at this from that point of view, may be a few days, but I will circle back.  That being said, I suspect the underlying issue on this is going to be the data model I think, and I am pretty sure you are not going to have access to change that etc.  Let me see what I can do though to try to confirm things.

Cheers,
Brett

To help users find verified answers, please don't forget to mark a correct resolution or answer to your problem or question as correct.
Dpage
New Contributor III

Re: Sort by Expression

Thank you very much!

Support
Support

Re: Sort by Expression

No promises, but I did get up with folks on my side, and they will bring this to the attention of Intuit, and we may have one of our consultants have a look at this post as well to see if he can potentially come up with something that might help you.  It may take a number of days, that person is quite busy unfortunately, but he will definitely let me know or leave a post for you directly once he can get to things.  Sorry I do not have anything better for you, but we'll see what we can do here.

Regards,
Brett

To help users find verified answers, please don't forget to mark a correct resolution or answer to your problem or question as correct.
Employee
Employee

Re: Sort by Expression

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:

Properties Panel.png

 

Resulting Table:

Sorted Tabe.png

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

Tags (3)
Dpage
New Contributor III

Re: Sort by Expression

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.Capture 1.PNGTop of the table. 0 qty but Null Reorder and Build points. Prefer to see these last.Capture2.PNGMy dimensions. Will use expressions to exclude some items once I figure out the sort expression.Capture3.PNGI added the expression you sent me.Capture4.PNGFurther down the table I am still seeing some mixing of items with Qty.s greater than their par level

Thank you for all of your help. Trying to turn QB into a Materials Requirement Planning tool. 🙂

Employee
Employee

Re: Sort by Expression

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.

QC_Intuit_1.png

Before:  Null/Zero rows included

QC_Intuit_0.png

After: Null/Zero rows excluded

QC_Intuit_1b.png

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.

QC_Intuit_3.png

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.

QC_Intuit_4.png

This should get you closer.

Regards,

Jeff

Dpage
New Contributor III

Re: Sort by Expression

Thank you for the information. I will work with what you have given me and see if I can get the result I want. The len function for Reorder and Build points wiped the table clear. I'm not exactly sure why but I have used calculated dimensions with Suppressed null values to filter and that was helpful in other areas. 

Thanks again. I will continue to work and let you know what I find.