3 Replies Latest reply: Jun 24, 2013 2:41 AM by ILARIA CREMONESI

# 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.

• ###### Re: Set Analysis with expression

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.

• ###### Re: Set Analysis with expression

Dear Gysbert,

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.

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.

• ###### Re: Set Analysis with expression

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