Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
any idea how to calculate the number of total days which are based on the status code.
I would like to show the total number of days of whose which are in StatusCode 02. (in script/set analysis?)
StatusCode | Status | Date | Count days | |
02 | StatusA | 13.5.2017 | ||
07 | StatusB | 4.9.2017 | 114 | |
02 | StatusA | 2.12.2017 | ||
07 | StatusB | 19.9.2018 | 291 | |
02 | StatusA | 19.12.2018 | ||
07 | StatusB | 1.2.2019 | 44 | |
02 | StatusA | 1.5.2019 | ||
07 | StatusB | 30.5.2019 | 29 | |
478 | StatusA days total |
You can also try this where you calculate the NoOfDays in the script and then use set analysis to show the count of only StatusCode = 02
Script
Table:
LOAD * INLINE [
StatusCode, Status, Date
02, StatusA, 13.5.2017
07, StatusB, 4.9.2017
02, StatusA, 2.12.2017
07, StatusB, 19.9.2018
02, StatusA, 19.12.2018
07, StatusB, 1.2.2019
02, StatusA, 1.5.2019
07, StatusB, 30.5.2019
];
FinalTable:
LOAD *,
Alt(Previous(Date), Date) - Date as NoOfDays
Resident Table
Order By Date desc;
DROP Table Table;
and then use this expression
=Sum({<StatusCode = {'02'}>}NoOfDays)
hi
in your script after you loaded the table with status and date, for the example i'll call it Table1.
the write this part in your script
Table2:
load * ,
if(previous(StatusCode)=2,Date-Previous(Date) ) as Count_Days
resident Table1
order by Date;
drop table Table1;
we use resident because it's the only way to use order by call
You can also try this where you calculate the NoOfDays in the script and then use set analysis to show the count of only StatusCode = 02
Script
Table:
LOAD * INLINE [
StatusCode, Status, Date
02, StatusA, 13.5.2017
07, StatusB, 4.9.2017
02, StatusA, 2.12.2017
07, StatusB, 19.9.2018
02, StatusA, 19.12.2018
07, StatusB, 1.2.2019
02, StatusA, 1.5.2019
07, StatusB, 30.5.2019
];
FinalTable:
LOAD *,
Alt(Previous(Date), Date) - Date as NoOfDays
Resident Table
Order By Date desc;
DROP Table Table;
and then use this expression
=Sum({<StatusCode = {'02'}>}NoOfDays)
Wau Thanks a lot!