without looking at your data and workbook, have you tried calculating total number opened in week and total number closed, then open minu closed for net change, then a simple running sum along weeks of net change should give you the numbers you want, important to note if you only want to show a certain period you will need to create a calculated field for the weeks you want to show and hide (not filter) the prior weeks to give you the correct figures in the weeks you do show.
I have tried to do so, but the end result is not correct. ( the attached twbx). the problem show when i have to eliminate the closed issues in subsequent weeks.
This is a very interesting issue and while there are several approaches that might be considered (you mentioned LOD in the title -- and it almost works but not when you need to be able to determine status across weeks), but this was the approach I took:
1. Create a Lookup Last Status calculation to determine the previous status of an issue. That allows us to consider whether a calculation was previously open or closed (or if this is the first time we're seeing this issue). That will enable us to keep the counts accurate.
It's just the code LOOKUP(MIN([Status]), -1) and is computed within the partition of Issue (select every dimension except Issue).
2. Create a Count Calculation to give a 1 if open, a -1 if closed, and a 0 if it shouldn't be counted (e.g. Closed as the first known status or Open when it was previously open
The code here is a bit complex, but basically it's just checking to see what the current status is, what the previous one was and whether it is Open (when previously closed or for the first time) or Closed (when it was previously Open). Otherwise, it won't get counted.
This calculation will also be computed along every dimension except Issue (because we don't want to cross over Issues to lookup the previous status)
3. Next, we'll create a Running Count calculation to just keep a running total.
This calculation just takes the 1, -1, or - value of Count and keeps a running total. It is computed Table Down (or along all dimensions), but it has the Count dimension nested, so you'll want to make sure that the nested table calc is computed with Issue as a partition (as shown above).
4. Notice that the final values for each week are now the values you are looking for! So, to keep only those values, we'll just filter using a Last() calculation computed along every dimension except Status Date Week and keep where Last() == 0
5. Hide the Headers for fields you don't want to see (they have to remain in the view to enable all the table calcs to work!) and you have your goal:
Hope that helps!
Status_history.twbx 20.8 KB
Thanks for this awesome explanation. Two more things not working for me, i want to show the weeks as line chart. When trying to do so, the sheet gets colored in Red (all formulas and fields are marked wrong) . How to convert it to line chart ?
Also, i want to get the percentage difference of this running count from previous week. so my line chart tool tip will show both (running count and Pct difference).
as an extra thing : i want to get the last week to be shown in a separate sheet so that i can put it a lone on the dashboard as text KPI ( showing last week percentage difference from last week).
i knocked up the attached based on single dataset calculation similar to Joshua's.
It does most of what you need, i realised after looking at your data that my assumption that a ticket could be opened once and closed once was incorrect, so need to build in a more sophisticated calculation to handle that a ticket can be closed when it isnt open and opened when it is already open. Will have a crack at that later but for now, here is where i got to with creating your line chart and floating 'latest week KPI box'.
The issue you have with Joshua's approach is that it relies on a table calculation and you would need to materialise the information and then chart it to create a line chart, essentially we need to find a way to bring in 'Prior status' to each row to make the calculations work.
I see you provided this in excel but is your true datasource a database where you could join it to itself or use a row calculation to bring the prior status alongside the current one?
If you can bring them alongside (something I have been unable to achieve in Tableau without table calcs that subsequently can't be turned into the charts you want) then pls see attached as the potential output.
Status_history.twbx 199.5 KB
Thanks guys for the valuable information.
I am lucky that my data source is an oracle database in which i moved the logic that Joshua gave and put it in the query itself. ==>
select lag(status) over (partition by issue_num order by status_change_date_id) as previous_status from table a ......
and then calculate the counter in an outer select. Afterwards, Only doing a running sum in Tableau for the counter.
That made it easier to chart in the way i wanted as line chart.