Best date interval structure?

[valpassos]

Hi community,

I have the following data with events (states) dates:

ProductID	FromState	ToState	StartDate	EndDate
2	Draft	Assigned	01/01/2020	05/01/2020
2	Assigned	Review	05/01/2020	07/01/2020
2	Review	Certification	07/01/2020	08/01/2020
2	Certification	Approved	08/01/2020	10/01/2020

This is how have my data right now (after modeling). I want to be able to calculate the duration (days) in each state and I don't know if this is the right structure to be able to do that. The regular duration formula would be, for instance for the Review state:

INTERVAL(MAX({<FromState={Review}>} EndDate) - MIN({<FromState={Review}>} StartDate),'d')

(the states can repeat themselves)

I would have to do this for every state. Is this the optimal data structure for this type of requirement? Is this asking for an IntervalMatch?

Thanks in advance for your comments!

Lisa

[Ksrinivasan]

hi,

you can use as given in measures, it will calculate how many days taken to complete the status,

[Usama]

Hi,

Try this:

Backend:
 Num(StartDate) as Start_DateNum,

 Num(EndDate) as End_DateNum

Frontend:

End_DateNum - Start_DateNum