Dean, I'm almost certain one of the gurus can help you can arrive at the values you describe, but you'll need to post a packaged workbook with some dummy data for them to be able to help you.
Attached is a very rudimentary sample. I used 3 states (Begin, In Process and Done). I have used In Process as the example.
Essentially on sheet 2 I am looking to go one level deeper that the difference calculations and depict the actual numbers that combine for the difference. In this example my Week 2 difference is Plus 3 and that is the results (3 Changes to this State, 1 Change From this State and 1 Stayed the Same).
I hope this makes sense.
Sample.twbx 5.5 KB
See the attached. The basic process is the common one used when we want to count something at one level of detail (state changes in this case) and then sum them up at a different level of detail, we use a nested table calculation. In this case, there is a table calculation that for each check of the state change returns 1 or Null, and the Compute Using for this calc is the Week (so it partitions on each ID). Then for each check there's a wrapper calc that uses something like:
IF FIRST()==0 THEN
WINDOW_SUM([Changed To Flag])
The Compute Using for this calc is on ID so it sums up all the flags for the week, retaining the nested calculation that addresses on the week.
I added an additional First Filter calc with the formula FIRST()==0 with a Compute Using of ID, this removes the extra data that is necessary for the computations to work but not needed for the final view.
The workout worksheet shows all the calcs, you can see one set of results in the view worksheet.
There were a few questions/comments I had on this:
- The Sheet 2 where you had calculated a difference from prior is not counting what you think it is. Because you have a regular filter on Status "In Process", that is only what is being returned to Tableau. So the difference from prior calc is counting 2 IDs in week 1 that had In Process Status and 5 IDs in week 2 status, it's not actually tracking changes from one week to another. You can see this in the "in process" worksheet.
- The totals you describe for Week 2 don't match the data. Looking at the Status changes for each ID, I count 4 changes to in Process, and one away from In Process, and one the same. You can see this in the "in process changes" worksheet.
- Based on your statements it seemed like you weren't trying to track which states each ID changed from and to, but more that there were state changes. If you actually want to count the state changes for each state and what it came from or went to, that would require a different route.
- Related to that, I didn't completely understand what you meant by "changes to this state", if you really want to be tracking "how many went from X to Y" then a different method would be necessary. In that case, one question is whether you are guaranteed to have every possible combination of state changes (all 28 of them) in the data for a given timeframe, and whether you have to show all those combinations regardless of whether or not they are in the data, or whether you just want to show totals of the state changes that exist.
counting state changes.twbx 45.0 KB