topic Re: Month Diff between max fromdate and min todate in QlikView

Month Diff between max fromdate and min todate

capriconuser — Sat, 16 Nov 2024 02:07:38 GMT

From below data i want to get month difference between whatever the minimum date is in fromdate and whatever the last max date in todate.. how i get that . .

POLIYNO From Date To Date 114371 21-05-2019 30-06-2019 114371 03-04-2019 30-06-2019 114371 08-12-2018 30-06-2019 114371 01-07-2019 31-12-2019

Re: Month Diff between max fromdate and min todate

ankitbisht01 — Thu, 24 Oct 2019 10:17:52 GMT

How you want the output to look like?, please paste required output in next column.

Re: Month Diff between max fromdate and min todate

capriconuser — Thu, 24 Oct 2019 10:26:19 GMT

POLICYNO From Date To Date Month_Difference CAR/114371 08-12-2018 30-06-2019 6 CAR/114371 03-04-2019 30-06-2019 2 CAR/114371 21-05-2019 30-06-2019 1 CAR/114371 01-07-2019 31-12-2019 11

In last record 11 because from min date 8-12-2018 and max date 31-12-2019 total 11 months is the difference .. so i want that difference only in last record of each policy

i tried this

Table: LOAD POLICY_NO, fromdate, todate , Num(fabs ( ( (year( fromdate) * 12) + month( fromdate) ) - ( ((year(todate) * 12) + month(todate)) ) )) as Month_Difference, FROM temp_table.qvd (qvd); table3: NoConcatenate LOAD * Resident Table Order By fromdate,todate asc; DROP Table Table; left Join (table3) LOAD POLICY_NO, Min(fromdate) as Min_Date, Max(todate) as Expiry_Max_Date Resident table3 Group By POLICY_NO; Store table3 into [C:\Users\HP\Desktop\test\table_3.qvd](qvd); Drop Table table3;