Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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())
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())
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
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!
Yes, that i what I changed in the script
SET DateFormat='DD-MM-YYYY';
Fine, It works
Thank so much
Neville