Timeline graph or timeline

[marwen_garwachi]

Hello community,

I need to show the number of days spent by status by case.

for exemple Status ID's are from 30 to 53 :

LOAD

CASE_NUMBER,

DTE_LAST_30,

DTE_LAST_33,

DTE_LAST_40,

DTE_LAST_42,

DTE_LAST_43,

DTE_LAST_50,

DTE_LAST_51,

DTE_LAST_52,

DTE_LAST_53

resident ACTIONS

so i need to show for each case the number of days spent in each status in a graph.

did someone did this before ?

Thanks for your help !

[Anil_Babu_Samineni]

What are the number of days over here

[marwen_garwachi]

The number of days between DTE_LAST_30 and DTE_LAST_33,

DTE_LAST_33 and DTE_LAST_40 etc...

[Anil_Babu_Samineni]

What is the logic behind, Because you are not giving dates to calcuate the number of days

[marwen_garwachi]

Each Case number had several status, so every status matches a date. I'm trying to display in a graph the average length of time a file is in each status. So to find the duration in days between 30 and 33 -> DTE_LAST_33-DTE_LAST_30 . 

[MarcoWedel · Accepted solution]

Hi,

maybe one solution could be:

tabStatusDates:
LOAD *, DayName(DTE_LAST_52+Rand()*100) as DTE_LAST_53;
LOAD *, DayName(DTE_LAST_51+Rand()*100) as DTE_LAST_52;
LOAD *, DayName(DTE_LAST_50+Rand()*100) as DTE_LAST_51;
LOAD *, DayName(DTE_LAST_43+Rand()*100) as DTE_LAST_50;
LOAD *, DayName(DTE_LAST_42+Rand()*100) as DTE_LAST_43;
LOAD *, DayName(DTE_LAST_40+Rand()*100) as DTE_LAST_42;
LOAD *, DayName(DTE_LAST_33+Rand()*100) as DTE_LAST_40;
LOAD *, DayName(DTE_LAST_30+Rand()*100) as DTE_LAST_33;
LOAD RecNo() as CASE_NUMBER,
DayName(Today()-Rand()*1000) as DTE_LAST_30
AutoGenerate 30;

tabStatusDatesTemp:
CrossTable (StatusID, Date)
LOAD * Resident tabStatusDates;

tabStatusDates2:
LOAD CASE_NUMBER,
     SubField(StatusID, 'DTE_LAST_', 2) as StatusID,
     Date,
     If(CASE_NUMBER=Previous(CASE_NUMBER),Previous(Date)-Date) as StatusDays
Resident tabStatusDatesTemp
Order By CASE_NUMBER, Date desc, StatusID desc;

DROP Table tabStatusDatesTemp;

hope this helps

regards

Marco