8 Replies Latest reply on Jul 5, 2017 3:06 PM by Shesil Parmar

# Find number of days between status change

Tableau community,

I’m struggling to work out some calculation; hope you guys can assist.

So I want to calculate, how many Days IDs stays Active and then how many days it stays from Active to Stopped and from there how many from Stopped to Closed and again Closed to Active .

The time period starts from 01/01/2017, so for example, if the ID’s first entry is on 6/1/17 as Stopped then count as it was Active for 5 days, if its closed then count as 5 Stopped days

If the status is Active only throughout the period then it should not be in the view.

There are 100s of record but I have trimmed it for this example.

Thank you

• ###### 1. Re: Find number of days between status change

Hi,

something like this?

Please find attached book for reference, hope that helps.

• ###### 2. Re: Find number of days between status change

Hi, Shesil

Workbook attached with 10.2, please let me know if you have question.

ZZ

• ###### 3. Re: Find number of days between status change

Thanks Zhouyi Zhang

This is somewhat i needed. two things

1) Is there anyway to remove ID , i have got 100s of record and dont need to report based on ID but by Month

2) so if the ID’s first entry is on 6/1/17 as Stopped then count as it was Active for 5 days, if its closed then count as 5 Stopped day (in your example above if the first entry is Stopped its counting number of days as stopped !)

• ###### 4. Re: Find number of days between status change

Something in here might help : FAQ:  Open & Close Dates

• ###### 5. Re: Find number of days between status change

Hi, Shesil

To your question, if remove ID, what's the end result going to be? e.g. by month is not a good idea for your case, because the status may cross the end of previous month and continue to the early next month, do you know to you will handle this case?

To your second question, it doable with my solution currently.

ZZ

• ###### 6. Re: Find number of days between status change

Thanks Zhouyi Zhang

i should have mentioned before that i want status change by month, so for example if the status is closed on 28th June 17, the it should count as 3 , and if its become active again on 5th of July , then for July count as close from 1 to 4, and Active from 5th and so on.

• ###### 7. Re: Find number of days between status change

Hi, Shesil

Sorry about the late response, please see my updated solution and result below.

Workbook attached for your reference. Please let me know if you have further question.

ZZ

1 of 1 people found this helpful
• ###### 8. Re: Find number of days between status change

prefect... thanks heaps Zhouyi Zhang