Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
krithikarees
New Contributor III

Expression for table box

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 :

8 Replies
Highlighted
vishsaggi
Esteemed Contributor III

Re: Expression for table box

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.

Highlighted
shiveshsingh
Honored Contributor

Re: Expression for table box

Can you share data?

Highlighted
krithikarees
New Contributor III

Re: Expression for table box

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

What I would like to see is something like this

Highlighted
vishsaggi
Esteemed Contributor III

Re: Expression for table box

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
Honored Contributor

Re: Expression for table box

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

Highlighted
vishsaggi
Esteemed Contributor III

Re: Expression for table box

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

Capture.PNG

Highlighted
vishsaggi
Esteemed Contributor III

Re: Expression for table box

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:

Capture.PNG

Expr:

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

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

      )

Highlighted

Re: Expression for table box

This sounds to me like

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)