Max Date Minus Min Date

[karthikoffi27se]

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_DATE	TO_DATE
01/01/2018	31/12/2018
01/01/2018	31/12/2018
01/01/2018	31/12/2018
01/01/2019	31/12/2019
01/01/2019	31/12/2019
01/01/2019	31/12/2019
01/01/2020	31/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_DATE	TO_DATE	Max(TO_DATE)-Min(FROM_DATE)
01/01/2018	31/12/2018	1095
01/01/2018	31/12/2018	1095
01/01/2018	31/12/2018	1095
01/01/2019	31/12/2019	730
01/01/2019	31/12/2019	730
01/01/2019	31/12/2019	730
01/01/2020	31/12/2020	365

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

Many Thanks

[Frank_Hartmann · Accepted solution]

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;