6 Replies Latest reply: Oct 27, 2013 4:31 AM by Michele Barini RSS

    selection

    Luisa Rossi

      Hi,

      need help.

      I have to select a record the previous to respect the last record.

      my table is, for example:

      id    rif

      1    31/01/2013

      2   31/03/2013

      3  5/06/2013

      4 18/08/2013

      that is,

      actual record is determined so:

      =Text(Month(max(RIF)))& Text(Year(max(RIF)))=18/08/2013

       

      but i have to select also last-1.

      i can create a field ID, but how i can selection last-1? now, return 18/07/2013

      I hope I explained

      thank so much

        • Re: selection
          Javier Florian

          If your work with a table, pivot table, graph... you can use below function:

          =Below(RIF)

           

          Please, let me know if this work for you.

           

          - JFlorian

          • Re: selection

            We can deal with the situation of sigiente ways.

             

             

            At the end of our script, we can create a variable that always has the value of the previous month:

             

             

            LET vPriorMonth = 'Month (AddMonths (Max (fulldate), -1))', / / Previous month in letters

            LET vPriorMonthYear = 'Year (AddMonths (Max (fulldate), -1))', / / Year for the previous month

            • Re: selection

              Hi Paola,

               

              I am not sure, why you has used Max month & Max year in your above expression.

              If I am understand you correct then, the below expressions can help you.

               

              For Max date value from "rif" field  =Date(max(rif))

              For previous month of Max date =AddMonths( Date(max(rif)),-1)

               

              Please explain bit more if this is not you are looking for?

                • Re: selection
                  Luisa Rossi

                  Hi,

                  thanks to all for your reply, but i didn’t explain exactly what i need.

                  In to the column “rif”, that contains date field, i want to extract by month-year, i don’t have all the months but only some months for exaple:

                  31/12/2012

                  23/02/2013

                  29/10/2013

                  If i need to calc expressions only for the last period i don’t have any issue (=Text(Month(max(RIF)))& Text(Year(max(RIF)))=ott2013) buti f i need to calc only the previous time interval i need to help.

                  I suppose to use addmonth(-1) but the previous time interval isn’t always the previous month.

                  I dont’ have any result if i try to calculate a field like IR_TOTALE. I supposed to create another table (called ID) where i can record the inserts, in this case if want to consider only the previous month i can consider max(id-1) but i don’t know how to calculate the max(id-1) and the related RIF.

                  Finally i need also to calculate the previous period in a form like “feb2013” and not like a number.

                    • Re: selection
                      Tresesco B

                      Not very clear, still I guess you can try MonthName() function. This gives you proper date(internally numeric) format output which allows numerical comparison or any date function operation(like AddMonths()); so you could refer previous month easily.

                      • Re: selection
                        Michele Barini

                        Hi,

                        maybe working on this:

                         

                        Tab3:

                        Load * inline

                        [id,    rif  

                            1, 31/01/2013  

                            2, 31/03/2013  

                            3, 5/06/2013  

                            4, 18/08/2013]

                        ;

                         

                        Load *,

                            id-1 as idPrec,

                            date(rif,'MMMYYYY') as Dt

                        resident Tab3

                        ;

                         

                        Load id as idPrec,

                            rif as rifPrec

                        resident Tab3

                        ;

                         

                        you could obtain something of similar: