Number of month from days

[Anonymous]

How can I calculate time outstanding in months based on days onhold?  This is what was being done in the old code but it's not working.

[Days Outstanding],

[Duration (Days)],

[Days Onhold])/30 as [Time Outstanding in Months];

[vishsaggi]

Can you share some sample data and show what is not working and your expected output.?

[Anonymous · Accepted solution]

Status = On Hold

Opened = 6/25/2005

Delivered Date = blank

Days on Hold = 4568

Time Outstanding = 0 ..... but the code is [Days Onhold])/30 as [Time Outstanding in Months] and it's zero

[vishsaggi]

Did you get the answer you are looking for?

[Anonymous]

No, looks like I did something wrong & marked my question correct.

[vishsaggi]

You can go back and edit it from Actions. So how did you get the value 4568 ? Is that a calculated value or a field from data table?

[Anonymous]

If([Detailed status] = 'On Hold - M',

      Ceil(

              If(Len(Trim([Delivered Date])) = 0, Today(),

              If([Delivered Date] < Opened, Opened, [Delivered Date])) - [Opened])) as [Days Onhold],

[vishsaggi]

ok. Can you do a preceding load and check if that works like

Preceding Load

YourtableName:

Load *, [Days Onhold]/30  AS TimeOutstandingInmonths;

LOAD ....,

          YourIFStatementfor Days on hold    AS [Days Onhold],

          otherfields

From yoursource;

Or a residentLoad like

YourtableName:

LOAD ....,

          YourIFStatementfor Days on hold    AS [Days Onhold],

          otherfields

From yoursource;

LEFT JOIN(YourtableName)

LOAD [Days Onhold],

           [Days Onhold]/30  AS TimeOutstandingInmonths

Resident YourtableName;