Derive new field

[Anonymous]

Hi have contract term,Contract term Details

if i select 42 Months ,in contract Term Details it should show like

Year 1 of 4 years

Year 2 of 4 Years

Year 3 of 4 years

Year 4 of 4 Years

Like this .

how to achieve this

42 Month we need to take as 4 Years

[jagan]

Hi,

Can you attach some sample data and your expected output?  Where you want this in script or in chart?

Regards,

Jagan.

[Anonymous]

pFA

[Anonymous]

hi jagan i attached sample application

Thanks in advance

[jagan · Accepted solution]

Hi,

Try this script

Tab1:

LOAD

rowno() as id,

  Year,

     Month,

     [Project Status],

     [Supplier(s)],

     [Description

Project

Event Name],

     Category,

     [Sourcing

Lead],

     [Business

Owner],

     [Budget/Forecast],

     [Managed

Spend],

     Savings,

     [Cost

Avoidance],

     [In-Year Savings],

     [Savings

%],

     [Capital

vs.

Expense] As [Captital vs.Expense],

     [Contract Term],

     If(WildMatch([Contract Term], '*Months*'), Ceil(KeepChar([Contract Term],0123456789)/12), Num(KeepChar([Contract Term],0123456789)))  as MaxNum,    

     [Savings Year2],

     [Savings Year3],

     [Savings Year4],

     [Savings Year5],

     [Savings Year6],

     Comments

FROM

[PO Spend and Saving Dashboard\Global_Savings_Executive_View.xlsx]

(ooxml, embedded labels, table is [Raw Data]);

Regards,

Jagan.