5 Replies Latest reply: Jan 11, 2018 12:33 PM by NEVILLE DHAMSIRI RSS

    How to add age table to see the Years of Age as at Today

    NEVILLE DHAMSIRI

      Dear All!

       

      Attached table carries the data as per Vehicle Register maintained & I look forward to bring in an age table to calculate the number of Years of each vehicle.If the Age > 5 Years I need that record to be highlighted with a back ground colour. Please help me on this.

      Regds

       

      Neville

       

       

        • Re: How to add age table to see the Years of Age as at Today
          Sunny Talwar

          Created Age in the script

           

          ASSET:

          LOAD [BR_CODE (AS PER OUR REORDS)],

          [Inward Date],

          [Purchased Date],

          Age(Today(), [Purchased Date]) as Age,

          [Asset Ref. No.],

              [Asset ID No.], Asset_Description, [Dep. Comm ence Date], [Dep. Rate], [Opening Balance],

              Additions, Inwards, [Opening Balance1] ,  [Opening Balance]+  Additions+  Inwards AS Cost, IF( [Opening Balance]+  Additions+  Inwards >300000,'Car', 'Bike')as Vehicle_Type1, [Current Year Value], [Written Down Value],

              [Inward Transfer], [Outward Transfer],IF([Inward Transfer]>0,'Inward',IF([Outward Transfer]>0,'Out ward')) As Inward_Outward,  [Sales Value], [Profit/ Loss Value], [T.R. Value],

              [Net Written Down Value], STATUS ,IF(STATUS='GA','Garage',IF( STATUS='ID','Idling',IF( STATUS='IW','Inward',IF( STATUS='SD','Sold',IF(  STATUS='US','Usable',IF(STATUS='YD','Yard')))))) as New_Status, [Physically Left Branch], User, [Date kept at Yard]

          FROM [MOTOR_VEHICLE_REGISTER.xlsx] (ooxml, embedded labels, table is Master);

           

          and then used a background color expression

          =If(Age > 5, Blue())


          Capture.PNG

          • Re: How to add age table to see the Years of Age as at Today
            NEVILLE DHAMSIRI

            Thanks Sunny for your information but I think I should have done something wrong somewhere that may be why I am not getting respective ages against each vehicle. Could you please correct see where I have gone wrong!

             

            Regds

             

             

            ASSET:

            LOAD [BR_CODE (AS PER OUR REORDS)], [Inward Date], [Purchased Date],  Age(Today(), [Purchased Date]) as Age,[Asset Ref. No.],

                 [Asset ID No.], Asset_Description, [Dep. Comm ence Date], [Dep. Rate], [Opening Balance],

                 Additions, Inwards, [Opening Balance1] ,   [Opening Balance]+  Additions+  Inwards AS Cost, IF( [Opening Balance]+  Additions+  Inwards >300000,'Car', 'Bike')as Vehicle_Type1, [Current Year Value], [Written Down Value],

                 [Inward Transfer], [Outward Transfer],IF([Inward Transfer]>0,'Inward',IF([Outward Transfer]>0,'Out ward')) As Inward_Outward,  [Sales Value], [Profit/ Loss Value], [T.R. Value],

                 [Net Written Down Value], STATUS ,IF(STATUS='GA','Garage',IF( STATUS='ID','Idling',IF( STATUS='IW','Inward',IF( STATUS='SD','Sold',IF(  STATUS='US','Usable',IF(STATUS='YD','Yard')))))) as New_Status, [Physically Left Branch], User, [Date kept at Yard]

            FROM [D:\MOTOR_VEHICLE_REGISTER.xlsx] (ooxml, embedded labels, table is Master);

            • Re: How to add age table to see the Years of Age as at Today
              NEVILLE DHAMSIRI

              It seems that the difference of  date format. The age I am getting on format on MM/DD/YY. Could you please show me how this could be corrected!