Subtract Dates

[Anonymous]

Hello Community,

I’m trying to subtract two dates and tried multiple ways still did not work. Please can someone hep me with this.

Based on a condition derived two date fields  “Already Completed” and “will complete”  from the “Approval date” field, this is fetching values.  But when I try to subtract “Already Completed” from “will complete” in a resident load it’s not bringing any data . Please can someone help me in finding out what’s wrong with my code?

Attached qvw and excel with test data. Thanks,

Harini

[vishsaggi]

With your First Load statement you are getting this data:

LOAD ID,

[Line Item],

Type,

Status,

[Approval date],

if([Type]='already completed',[Approval date]) as [already completed Date],

if([Type]='will complete',[Approval date]) as [will complete Date]

FROM [..\Desktop\Test2.xlsx] (ooxml, embedded labels, table is Sheet1);

What is your expected output should be?

[Anonymous]

Hi,

Thank you for the response.

Sorry my initial test data set was not correct, I have updated the excel and qvw with correct data set.

As show in the attached excel and qvw:

for example  ID 800 has two line Items  “already completed” and “will complete”.

I’m trying to calculate the number of days it took to move the line item from “already completed” to will complete” .

So I’m trying to subtract “already completed Date” from “will complete Date”

Expected Result:

6/11/2016 -6/11/2015 = 365 days

Thanks,

harini

[vishsaggi · Accepted solution]

Try this and check?

Main:

LOAD ID,

[Line Item],

Type,

Status,

[Approval date],

if([Type]='already completed',[Approval date]) as [already completed Date],

      if([Type]='will complete',[Approval date]) as [will complete Date]

FROM [..\Desktop\Test2.xlsx] (ooxml, embedded labels, table is Sheet1);

Tmp:

Load

  ID,

//[will complete Date]-[already completed Date] as Days

//Interval([will complete Date]-[already completed Date],'D') as Days

Min([will complete Date]) - Min([already completed Date]) as Days

Resident Main

GROUP BY ID;

[vvira1316]

Hi,

See if this will help.

NoConcatenate
EmpData:
LOAD
ID,
[Line Item],
Type,
Status,
[Approval date]
FROM
[..\Data\Test9.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
ACD:
LOAD
ID,
[Approval date] as [Already Completed Date]
Resident EmpData
Where [Type]='already completed';

NoConcatenate
WCD:
LOAD
ID,
[Approval date] as [Will Completed Date]
Resident EmpData
Where [Type]='will complete';

Expression in chart

=If(not IsNull([Will Completed Date]), [Will Completed Date] - [Already Completed Date])

ID	Status	Already Completed Date	Will Completed Date	# of Days
434	Approved	5/14/2015	5/12/2016	364
800	Approved	6/11/2015	6/11/2016	366

You may have to adjust data model as per your need.

[Anonymous]

This worked.Thank you,

Harini

[Anonymous]

Thank you for the response.

Thanks,

Harini