6 Replies Latest reply: Apr 4, 2011 10:38 AM by Deepak Vadithala RSS

    select in an expression or variable

      sorry for my english.

      I need a way to make a select x from table in an expression field or variable. Is it possible.

      My problem is that i have a items price list with "start date" and "end date".

      I made an input box where the user insert a date and in a tab pivot he wants the list of items that has validate price in that date.

      How can i make this work?

       

      tnx

      db

        • select in an expression or variable
          Deepak Vadithala

          db - This is possible by using the variables and using the same variable in the expression. Please can you share the sample data... I can give a go :)

          Cheers - DV

            • select in an expression or variable

               

              Tnx,

              i send you my file.

              if you insert 01-04-2011 into "data riferimento" i'd like that into the table remains only the rows with datainizio most near to the datariferimento (es. 01-03-2011).

              tnx a lot.

              db

               

                • select in an expression or variable

                  nobody can help me?

                    • select in an expression or variable
                      Richard Sheppard

                      Hi d_borghi,

                      Am relatively new to QV, so there maybe a better solution than the one attached....

                      --------------------------------------------------------------------------------------------------------

                      Changed the input box number format, to date

                      Created/tested text box to return the correct value from your input box/ASK_DATA variable

                       

                      Created new calculated dimensions for each of your reported dimensions.

                      For a field value to appear in your report, it first has to pass the following evaluation:

                      if DATAINIZIO >= ASK_DATA , then return field value, else null :

                      =if(DATAINIZIO>='$(ASK_DATA)',ARTICOLO,null())

                      For the date fields, remember to add the "date" syntax as follows:
                      =if(DATAINIZIO>='$(ASK_DATA)',date(DATAFINE,dd/mm/yyyy),null())

                      If you don't want to see the last row (null) with the value, in the dimensions tab, deselect "display when value is null"

                       

                      Is this what you were hoping to achieve?

                      Kind regards,

                      Rich

                      • select in an expression or variable
                        Deepak Vadithala

                        db - I am sorry I could not work on this one. I am little busy with my work. However, before I proceed any further on this one. I wanted to understand the issue completely.

                        RichSheppard already provided a solution which I assume is what you need...

                        However, I am working on using set analysis in your expression and not touching the dimensions. Please try to use this expression to search on manually entered date through a variable.

                        =COUNT({<DATAINIZIO = {'$(ASK_DATA)'}>} PREZZO)

                        And my understanding is that you need to get nearest (less than the current date) if there is no date available matching the manually entered date. Is this right?

                        Cheers - DV