How to calculate the number of dates based on the status code

[Arto_Kukkonen]

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

[lironbaram]

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

[sunny_talwar · Accepted solution]

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)

[Arto_Kukkonen]

Wau  Thanks a lot!