Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

nevilledhamsiri
Valued Contributor

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

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

1 Solution

Accepted Solutions
MVP
MVP

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

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

5 Replies
MVP
MVP

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

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

nevilledhamsiri
Valued Contributor

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

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 (ooxml, embedded labels, table is Master);

nevilledhamsiri
Valued Contributor

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

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!

MVP
MVP

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

Yes, that i what I changed in the script

SET DateFormat='DD-MM-YYYY';

nevilledhamsiri
Valued Contributor

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

Fine, It works

Thank so much

Neville

Community Browser