Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;