Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis with expression

Hello everyone,

I am trying to build an expression in a pivot table that looks like this:

min(   {$<VarianteVendMesc={"=  DataUltimoMovimento<=dtFineVarianteVendMesc and DataUltimoMovimento>=dtInizioVarianteVendMesc and PesoMescola*QuantVefi>=QPrezzoVendMesc"}> } PrezzoUnitarioVendMesc)

i.e. it should extract the value of "PrezzoUnitarioVendMesc" of a table only if other dimensions satisfy the conditions in the expression.

As an aggregation function, what should I use to get the exact value?

Min is not correct, I want the exact value which results from the conditions given.

The pivot table actually gives the value when one row is selected, but gives no value when no row is selected.

Thus, I am not sure this is the right path to follow, but I am trying.

Thank you in advance.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

The set for a set analysis expression is calculated once for the entire chart, not per row. So unless VarianteVendMesc uniquely identifies each individual record you can't use it to compare fields of the same record. See attached qvw for an explanation.


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Gysbert_Wassenaar

The set for a set analysis expression is calculated once for the entire chart, not per row. So unless VarianteVendMesc uniquely identifies each individual record you can't use it to compare fields of the same record. See attached qvw for an explanation.


talk is cheap, supply exceeds demand
Not applicable
Author

Dear Gysbert,

thank you for your reply.

I understand from the example you attached that, since OrderID uniquely identifies each row, the right expression would be sum({<OrderId={'=OrderDate=ShipDate'}>} Quantity)  with OrderId left to the equal sign.

Thus in my example I should find the dimension that uniquely identifies each row.

However each row first dimension is "Commessa", i.e. a production order that produces an article x.

This article is made of a component y.

What I need through the expression is the price of component y from a specific list price where the validity date of this list price comprehends the date of the production order.

Thus, I tried to put the IDVarianteVendMesc and not the VarianteVendMesc, because it would uniquely identify the list price. On the other hand, it does not identify each row, because the dimension of each row is the "Commessa", as it is the OrderID in your example.

Any suggestion?

Thank you very much.Example_set_expression.jpg

Not applicable
Author

In fact if I add as a dimension in the chart the "VarianteVendMesc" with a limit on the date of the list price compared to the date of the "Commessa", the expression would be simply "PrezzoUnitarioVendMesc" and it would be computed correctly