4 Replies Latest reply: Jan 26, 2015 12:43 PM by Alfonso Gaizo RSS

    how to compare single Date Column value with all values of another Date Column

      hello every one,

       

      I try to explain my problem, for privacy i cannot display my sample data, but my question is this:

       

      i have something like this table:

       

      ID          NULLCOLUMN          DATE     DATE-1workday

      1                      a                    11.12.13     10.12.13

      1                      -                     15.11.13     14.11.13                   

      1                      b                    07.10.14      06.10.14    

      2                      c                    23.11.12      21.11.12

      2                      d                    07.07.12      06.07.12    

      3                      -                     14.01.15      13.01.15    

      4                      -                     30.08.14      27.08.14  

      4                      -                     15.05.13      14.05.13

       

      I want to build  Pivot Table that has ID as dimension and one expression that counts the null values of NULLCOLUMN for all those DATE values <= than every value of "DATE-1workday".

       

      Have someone any ideas about the expression script?

        • Re: how to compare single Date Column value with all values of another Date Column
          Prashant Sangle

          Hi,

           

          Try with Indirect Set analysis

           

          try with p()

           

          Regards,

            • Re: how to compare single Date Column value with all values of another Date Column

              Hi Max,

               

              how i can use this?

               

               

               

              Can you explain?

               

              Thank you,

                • Re: how to compare single Date Column value with all values of another Date Column
                  André Gomes

                  Hi,

                   

                  You can only use p() and e() function in a set analysis set modifier.

                   

                  From the Help file:

                   

                  Set Modifiers with implicit field value definitions

                  In the above examples, all field values have been explicitly defined or defined through searches. There is however an additional way to define a set of field values by the use of a nested set definition.

                  In such cases, the element functions P() and E() must be used, representing the element set of possible values and the excluded values of a field, respectively. Inside the brackets, it is possible to specify one set expression and one field, e.g. P({1} Customer). These functions cannot be used in other expressions:

                  Examples:

                  sum( {$<Customer = P({1<Product={‘Shoe’}>} Customer)>} Sales )

                  returns the sales for current selection, but only those customers that ever have bought the product ‘Shoe’. The element function P( ) here returns a list of possible customers; those that are implied by the selection ‘Shoe’ in the field Product.

                  sum( {$<Customer = P({1<Product={‘Shoe’}>})>} Sales )

                  same as above. If the field in the element function is omitted, the function will return the possible values of the field specified in the outer assignment.

                  sum( {$<Customer = P({1<Product={‘Shoe’}>} Supplier)>} Sales )

                  returns the sales for current selection, but only those customers that ever have supplied the product ‘Shoe’. The element function P( ) here returns a list of possible suppliers; those that are implied by the selection ‘Shoe’ in the field Product. The list of suppliers is then used as a selection in the field Customer.

                  sum( {$<Customer = E({1<Product={‘Shoe’}>})>} Sales )

                  returns the sales for current selection, but only those customers that never bought the product ‘Shoe’. The element function E( ) here returns the list of excluded customers; those that are excluded by the selection ‘Shoe’ in the field Product.

                   

                  Regards

                   

                  André Gomes