No.of days between 2 column based on dimension qliksense

[UserID2626]

Hi,

How to find the diffrence between the 2 dates based on the dimension.

I need to show the chart in bar graph where monthname(end_date) as dimension and measure has to diff b/w 2 dates.

not getting expected result from this condition -  min(start_date)-max(end_date)

my requirement is for each ID need to take min and max for the month

sample date:

ID	abc	Start_date	End_date
A12077	A10004	18/06/2021 09:36:05	28/06/2021 06:26:29	9.868333333
A12078	A10005	18/06/2021 12:42:31	25/06/2021 07:08:04	6.767743056
A12084	A10006	21/06/2021 11:11:48	25/06/2021 08:35:27	3.891423611
A12085	A10007	21/06/2021 11:32:48	25/06/2021 07:34:24	3.834444444
A12086	A10008	21/06/2021 13:13:22	25/06/2021 07:14:43	3.7509375
A12090	A10010	22/06/2021 10:17:49	28/06/2021 06:15:48	5.83193287
A12109	A10018	25/06/2021 11:20:21	25/06/2021 11:43:48	0.016284722
A13032	A7017	11/06/2021 11:12:57	29/06/2021 15:01:02	18.1583912
A14025	A9002	11/06/2021 10:52:03	25/06/2021 13:49:34	14.12327546
			Expected result for jun 2021	66.2427662

[RsQK · Accepted solution]

Hi, you could solve this within the script like so:

temp:
LOAD
    ID,
    abc,
    Start_date,
    End_date
FROM [lib://data/testdata2609.xlsx]
(ooxml, embedded labels, table is Sheet1);

temp2:
LOAD *,
MONTHSTART(Start_date)	AS period
RESIDENT temp;

DROP TABLE temp;

LEFT JOIN (temp2)
LOAD *,
max_date-min_date as diff;

LOAD
ID,
period,
MIN(Start_date) AS min_date,
MAX(End_date)	AS max_date
RESIDENT temp2
GROUP BY ID,period;

and then in the frontend just do SUM(diff) as a measure.