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