Expression for table box

[krithikarees]

I have shipment data. Same product has been shipped and returned several times. I need to have a logic in place, where I need to calculate number of days between dates.

1) if a serial number does not have a return date  = today()- ship date

2) if a serial number has one return date = return date - ship date

3) if a serial number has multiple returns

a.      First ttf(time to failure) = 1^st return date– ship date = no. of days

b.      2^nd ttf = 2^nd return date – 1^st return date = no. of days

c.      3^rd ttf = 3^rd return date – 2^nd return date, etc = no. of days

d.      Suspension = todays date – last return date. = no. of days

example :

[vishsaggi]

You can use Interval() function to get no of days. Like

= Interval(date2 - date1, 'dd') something like this. Need some sample data to look into your request.

[shiveshsingh]

Can you share data?

[krithikarees]

This is what I have in my dashboard. However the numbers are wrong .

What I would like to see is something like this

[vishsaggi]

Rather than images can you post this as a table or in a text so that it will be easy for us to copy the data.

[shiveshsingh]

what are the expressions that you are using? It's really difficult to help you without data.

[vishsaggi]

Oh what criteria you want to see the dates for TimetoFailure with 1634, 275, 300? Based on your yellow highlighted data set in your above screenshot you can do something like this:

ForumTest:

LOAD Srl.No,

     Date(Date#(PanelShipDate, 'M/DD/YYYY')) AS PanelShipDt,

     Date(Date#(RMAIssueDate, 'M/DD/YYYY'))  AS RMAIssueDt,

     Num(Floor(Date#(PanelShipDate, 'M/DD/YYYY'))) AS ShipDtID,

     Num(Floor(Date#(RMAIssueDate, 'M/DD/YYYY')))  AS IssueDtID INLINE [

Srl.No, PanelShipDate, RMAIssueDate

Z0-D010-04B, 1/28/2011, 7/20/2015

Z0-D010-04B, 1/28/2011, 4/20/2016

Z0-D010-04B, 1/28/2011, 2/14/2017

];

use Straight table and add

Dim: Srl.No, PanelShipDt, RMAIssueDt

Expr: = IF(ISNULL(Interval(IssueDtID - Above(IssueDtID), 'dd')), Interval(IssueDtID - ShipDtID, 'dd'), Interval(IssueDtID - Above(IssueDtID), 'dd'))

You will get like below

[vishsaggi]

I am not sure if this is what you are looking for just thoroughly check and let us know if it works.

ForumTest:

LOAD *, AutoNumber(RMAIssueID, Srl.No) AS ReturnDtCnt;

LOAD *,

        Date(Num#(PanelShipDate),'MM/DD/YYYY') AS PanelShipDt,

        Date(Num#(RMAIssueDate),'MM/DD/YYYY') AS RMAIssueDt,

        Num#(PanelShipDate) AS PanelShipID,

        Num#(RMAIssueDate) AS RMAIssueID;

LOAD [Serial Number]  AS Srl.No,

    Date#([Panel Ship Date], 'M/DD/YYYY') AS PanelShipDate,

    Date#([RMA Issue Date],  'M/DD/YYYY') AS RMAIssueDate,

    [Time To Faliure]

FROM

[Faliure calc.xls]

(biff, embedded labels, table is Sheet1$);

Use straight table and add

Dim:

Expr:

= Fabs(IF(ReturnDtCnt = 1, Interval(RMAIssueID - PanelShipID, 'dd'),

       Interval(RMAIssueID - Above(TOTAL RMAIssueID), 'dd'))

      )

[Anil_Babu_Samineni]

This sounds to me like

[Time To Failure] - Above([Time To Failure])