I have two problems. I need to grab a field with my set analysis from outside my dimension. I'm pretty sure I've done this before but I cannot seem to find the function that allows that anywhere, after about 3 days straight of scouring and reading documentation.
The second issue I have is just a simple syntax problem on the value I'm trying to gather above. Here is a snippet of my formula from my table:
=aggr( // I don't know if this is necessary at all Concat({< // I have also tried Only() here with no success [Transactions.Ref Number] = ExpressionVariablePlaceHolder,
Transactions.Memo={"*Sales Order*"} >} Transactions.Memo // This value is never displayed, if I remove the Aggr() encapsulation I get "error in calculated dimension ),'' )
I need to grab all lines under [Transactions.Ref Number] and extract the one that contains "Sales Order: XXXXX". Currently each line of Transaction shows up as a separate row and only one contains the "Sales Order: XXXXX" Transactions.Memo value, all other rows show it as blank which is why I have to grab it outside the dimension of each row.
1. The preferred solution for this problem would be to use a "static" Dimension (whatever it might be) and to regulate the selection of transactions in the Measure(s). For example:
In addition, disable Zero lines in the chart properties.
2. If you REALLY want to solve it in a Calculated Dimension, then the right syntax of doing it is using the AGGR() function in combination with the Only() function and a set analysis condition inside the Only(). The Dimension inside the only() should be the same as the dimension of the AGGR().
For example:
AGGR(ONLY( {Set condition} Dimension), Dimension)
Just replace the Set condition with your own condition and replace Dimension with your own field name that you'd like to use as a Dimension.
In this case, also disable Null values for this Dimension.
If you have other, higher level, dimensions in the same chart, you should add them to the list of the AGGR dimensions.
Allow me to invite you to my lecture on Set Analysis and AGGR() at the Masters Summit for Qlik, that will take place online on March 1st. I will teach all of these techniques - the AGGR, the Set Analysis, the Calculated Dimensions, and also why you should be careful about using some of them in large scale apps.
The sum() function was an example of a numerical expression that aggregates some numeric metrics, such as Sales, Quantities, etc... If your goal is to show the text of the memo, then you should use one of the textual functions, such as Only(), MaxString, FirstSortedValue, etc.
If you could describe what information you are trying to display in your chart, at what level of aggregation, I'd be able to help you formulate it in terms of Dimensions and Measures.
The value that needs to be displayed is the text "Sales Order: XXXXX" (XXXXX being the number relevant to the transaction) which is contained in the field Transactions.Memo . Each transaction is split into multiple rows per line item and for whatever reason the Transactions.Memo field is effectively a line item meaning it is separated to its own row in the table but I need it represented in each row of the transaction it belongs to. That's why above I'm trying to do set analysis to grab the Transactions.Memo field by the Transactions.Ref Number and the contents of the Transactions.Memo field.
I don't have a firm enough grasp on the function of the Aggr function to answer the level of aggregation question. My understanding was that it is just supposed to combine the values it is given, like my set analysis above and the contents of '' which of course results in null because my analysis outputs nothing, as far as I can tell.
The AGGR() function is one of the most complex and the least understood features in Qlik. Even experienced developers can be confused with it... That's why I'm inviting everyone to listen to my lecture about AGGR at the Masters Summit for Qlik...
In your case, however, I'd recommend a scripting solution, rather than an AGGR() solution. If I understood your explanation correctly, each transaction is represented by multiple lines in the data, and one of those lines carries the Sales Order number. You need this number populated on all detailed lines for this transaction.
If you add this number to every line in your data load script, then the chart expressions can be simplified a lot.
You can do it either using JOIN or using a MAPPING table - both approaches are valid, pick the one that you are most comfortable with.
As a rule of thumb - building (or restoring) data associations using Data Modeling will always produce better (faster, easier, more sustainable) results than trying to associate data in chart expressions. Data modeling is another important discipline that Barry Harmsen teaches at the Masters Summit for Qlik.
Thanks for the information. Unfortunately this is not quite the right direction.
Your assessment of the memo (string containing "Sales Order: XXXXX) location is correct however I don't think manipulating the lines on the transaction is the right way to go here. I just need that memo field displayed in my straight table on every row relevant to the original transaction which is displayed over multiple rows depending on the number of line items.
The actual user facing issue here is the line item description needs to be viewed in conjunction with the memo field (sales order number) to tie these pieces together for the user. The problem is wherever there is an actual line item the memo field shows blank in the dimension. I think in the base software behind the scenes the memo field is actually another line item so the real line items and the memo will never exist in the same dimension/row.
I unfortunately do not have access to scripting at all either. I'm stuck with a 3rd party application that has Qlik built into it with a number of components removed.
Which is how I arrived here: AGGR(ONLY({<Transactions.Memo={"Sales Order*">}Transactions.Memo),'')
