11 Replies Latest reply: Dec 10, 2010 1:00 PM by Miguel Angel Baeyens de Arce RSS

    filter function

    rickvanewijk

      Hello,

      I have a problem i can't solve.

      I made a tabelobject what show the following information:
      - company name
      - contract_startdate
      - contract_enddate

      The function of the tabel is to show when a compony started there contract end when it ends.

      My question is: is it possible to create a function where i can put a specifik date, where the tabel reacts by showing only company names were contract_enddate is higher then <inputdate>.

      I hope someone can help me?

      Regrats,
      Rick

        • filter function
          Miguel Angel Baeyens de Arce

          Hello Rick,

          First, create a variable vDate in the menu Settings, Variable Overview.

          Then, create a slider/calendar object and set it to use variable vDate. Set the minimum to

           

          =Min(contract_enddate)
          and the maximum to
          =Max(contract_enddate)


          Go to the Number tab and set it to "Date"

          Now select any date in this object you have just created and check that the date is displayed correctly.

          If so, create a new chart, pivot table, add Company name as dimension and create two expressions:

           

          Only({< contract_enddate = {'>$(=Date(vDate))'} >} contract_startdate)


           

          Only({< contract_enddate = {'>$(=Date(vDate))'} >} contract_enddate)


          Now you should see only those companies and dates greater than the date you have selected in your calendar object. If you change the object, the chart will be changed dynamically.

          Hope this helps.

            • filter function
              rickvanewijk

              Miguel,

              Thanks for your fast reply.

              I've created a working slider/calender.

              But when i create the pivot table it doesnt show any results.

              any idea?

               

                • filter function
                  Miguel Angel Baeyens de Arce

                  Is the date shown dates correctly? What dimension and expressions do you have in your chart?

                    • filter function
                      rickvanewijk

                      the date shown dates correctly, The vDate veriable also change when i change date so i geuss it works.

                      I use Company name as dimension, with no start or end date.
                      and i use the expressions as you mention with a little change to make the names correct.

                       

                        • filter function
                          Miguel Angel Baeyens de Arce

                          Hello Rick,

                          You may post some sample data so we can draw a chart based on it.

                          Regards.

                            • filter function
                              rickvanewijk

                              Miguel,

                              I followed exact the first instruction you've posted but doenst work.
                              I don't understand what you mean with sample data.
                              What kind of information do you mean.

                              Regards,

                                • filter function
                                  Ensure that contract_enddate is recognized as a Date and not as a string.
                                  If not the solution suggest above will not work.
                                  • filter function
                                    Miguel Angel Baeyens de Arce

                                    Rick,

                                    1.- I'm using this data (script):

                                     

                                    Data:LOAD * INLINE [CompanyName, StartDate, EndDateA, 01/01/2010, 01/05/2010B, 01/02/2010, 20/02/2010C, 01/02/2010, 01/03/2010D, 01/04/2010, 30/04/2010];


                                    2.- Create a variable named vDate

                                    3.- Create a Slider/Calendar object using variable vDate, set minimum and maximum date as above.

                                    4.- Create a new chart, Pivot Table, select CompanyName as Dimension

                                    5.- Use the following as expression Start Date

                                     

                                    Only({< EndDate = {'>$(=Date(vDate))'} >} StartDate)


                                    6.- Use the following as expression End Date

                                     

                                    Only({< EndDate = {'>$(=Date(vDate))'} >} EndDate)


                                    Close the chart properties.

                                    Now select in your calendar a date. Select March 31, 2010. According to your description, it will show two results (the two companies which end date is greater than 31/03/2010): A and D, with their corresponding start and end dates.

                                    Hope this helps

                                      • filter function
                                        rickvanewijk

                                        Miguel,

                                        I know why this doesn't work.
                                        Our companies have at least 2 contracts.
                                        We test your example above it works.
                                        When we add for example company B 2 times (so 2 contracts) bouth will not be showed.

                                        So the 2the or more contracts caught the problem.

                                        Do you know how we can fix this so only the active contracts will be show also when a company has 1 active contract and 1 contract that has end?

                                          • filter function
                                            Miguel Angel Baeyens de Arce

                                             


                                            rickvanewijk wrote:Do you know how we can fix this so only the active contracts will be show also when a company has 1 active contract and 1 contract that has end?


                                            Rick,

                                            There may be several solutions:

                                            A) If "Active" is a field that already exists in your database, then use it in the set analysis

                                             

                                            Only({< Active = {'Y'}, EndDate = {'>$(=Date(vDate))'} >} EndDate)


                                            B) If the "Active" status depends on the end date itself, then something like the following

                                             

                                            Only({< EndDate = {'>$(=Date(vDate))<$(=Date(Today()))'} >} EndDate)


                                            I've used my code above for these new expressions, so you will have to customize them for your needs and field names.

                                            Hope this helps.