Max Date Minus Min Date

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

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

Re: Max Date Minus Min Date

Frank_Hartmann — Thu, 23 Apr 2020 08:58:18 GMT

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;

topic Max Date Minus Min Date in QlikView

Max Date Minus Min Date

Re: Max Date Minus Min Date