topic Re: Expression for table box in QlikView

Expression for table box

krithikarees — Wed, 25 Nov 2020 16:16:04 GMT

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 :

Re: Expression for table box

vishsaggi — Wed, 09 May 2018 18:58:22 GMT

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.

Re: Expression for table box

shiveshsingh — Thu, 10 May 2018 07:21:38 GMT

Can you share data?

Re: Expression for table box

krithikarees — Thu, 10 May 2018 14:38:35 GMT

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

What I would like to see is something like this

Re: Expression for table box

vishsaggi — Thu, 10 May 2018 14:49:18 GMT

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.

Re: Expression for table box

shiveshsingh — Thu, 10 May 2018 15:18:43 GMT

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

Re: Expression for table box

vishsaggi — Thu, 10 May 2018 19:27:37 GMT

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

Re: Expression for table box

vishsaggi — Thu, 10 May 2018 22:04:17 GMT

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'))

)

Re: Expression for table box

Anil_Babu_Samineni — Fri, 11 May 2018 01:54:35 GMT

This sounds to me like

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