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

    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.

        • Re: Set Analysis with expression
          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.

            • Re: Set Analysis with expression

              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