Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Max Date Minus Min Date

Hi All,

I need your quick help, I am trying to get difference between two dates, FROM_DATE and TO_DATE based on year selection, so basically if I select year 2018,2019,2020 I should get the difference between FROM DATE and TO_DATE

Say for example

FROM_DATETO_DATE
01/01/201831/12/2018
01/01/201831/12/2018
01/01/201831/12/2018
01/01/201931/12/2019
01/01/201931/12/2019
01/01/201931/12/2019
01/01/202031/12/2020

 

Year is calculated from FROM_DATE --> Year(FROM_DATE) AS YEAR

Now if i select year as 2018,2019,2020 it should take Min of FROM_DATE as 01/01/2018 and Max of TO_DATE as 31/12/2020 and calculate the difference as 1095 likewise it has to do for all the rows.

as per the below output.

FROM_DATETO_DATEMax(TO_DATE)-Min(FROM_DATE)
01/01/201831/12/20181095
01/01/201831/12/20181095
01/01/201831/12/20181095
01/01/201931/12/2019730
01/01/201931/12/2019730
01/01/201931/12/2019730
01/01/202031/12/2020365

 

Either the script query or dashboard query will be helpful, please advise.

Many Thanks

1 Solution

Accepted Solutions
Highlighted
Master II
Master II

Re: Max Date Minus Min Date

try like this:

 

tmp:
LOAD FROM_DATE, 
     TO_DATE       
FROM
[https://community.qlik.com/t5/New-to-QlikView/Max-Date-Minus-Min-Date/td-p/1696011]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

left join

Load date(max(TO_DATE)) as Max Resident tmp;

NoConcatenate
final:
Load *,Max-FROM_DATE as Diff Resident tmp; DROP Table tmp;

 

View solution in original post

1 Reply
Highlighted
Master II
Master II

Re: Max Date Minus Min Date

try like this:

 

tmp:
LOAD FROM_DATE, 
     TO_DATE       
FROM
[https://community.qlik.com/t5/New-to-QlikView/Max-Date-Minus-Min-Date/td-p/1696011]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

left join

Load date(max(TO_DATE)) as Max Resident tmp;

NoConcatenate
final:
Load *,Max-FROM_DATE as Diff Resident tmp; DROP Table tmp;

 

View solution in original post