topic Re: Subtract Dates in QlikView

Subtract Dates

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

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

Re: Subtract Dates

vishsaggi — Mon, 11 Sep 2017 20:23:43 GMT

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?

Re: Subtract Dates

Anonymous — Mon, 11 Sep 2017 21:09:30 GMT

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

Re: Subtract Dates

vishsaggi — Mon, 11 Sep 2017 21:30:02 GMT

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;

Re: Subtract Dates

vvira1316 — Mon, 11 Sep 2017 22:53:09 GMT

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.

Re: Subtract Dates

Anonymous — Tue, 12 Sep 2017 00:20:47 GMT

This worked.Thank you,

Harini

Re: Subtract Dates

Anonymous — Tue, 12 Sep 2017 00:21:33 GMT

Thank you for the response.

Thanks,

Harini