Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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) = 1st return date– ship date = no. of days
b. 2nd ttf = 2nd return date – 1st return date = no. of days
c. 3rd ttf = 3rd return date – 2nd return date, etc = no. of days
d. Suspension = todays date – last return date. = no. of days
example :
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.
Can you share data?
This is what I have in my dashboard. However the numbers are wrong .
What I would like to see is something like this
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.
what are the expressions that you are using? It's really difficult to help you without data.
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
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'))
)
This sounds to me like
[Time To Failure] - Above([Time To Failure])