7 Replies Latest reply on Dec 7, 2016 10:40 AM by ammar.khwaira

    LOD expression with window function ( to calculate last status before end of week)

    ammar.khwaira

      Hi

       

       

      I have a problem with window function to count open status issues.

       

       

      1) My data set is : history data of issues status updates.

      2) My objective is : at the end of each week , count the number of issues that left open

      3) in this case, i need to get the last status of each issue at each week, count the open issues (and deducting the ones that were closed)

       

       

      in the attached data set, the output should be a line graph of week number and count of open issues:

       

       

      Week 1 ( aug 15) , no of open issues = 2  (Issue A, Issue D)

      Week 2 ( Sep 26) , no of open issues = 2 (Issue B, Issue D from prev week) . note here that Issue A was closed , so it should not appear

      Week 3, (Oct 3)  , no of open issues = 2   (Issue B as it is still open, Issue D from week1)

      Week 4, (Oct 17) , no of open issues = 3  (Issue B, Issue D, Issue C)

       

       

      how can we do this ? the tricky part for me is to exclude the closed issues , and , if the issue do not have a record in the current week (like Issue D in week2,3,4) should be counted.

       

       

      i tried to subtract open - closed but the numbers are not correct.

        • 1. Re: LOD expression with window function ( to calculate last status before end of week)
          Chris Dickson

          Hi Ammar,

           

          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.

          • 2. Re: LOD expression with window function ( to calculate last status before end of week)
            ammar.khwaira

            Hi Chris

             

            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.

            • 3. Re: LOD expression with window function ( to calculate last status before end of week)
              Chris Dickson

              does the attached do what you need?

               

              the only issue i can see is that you don't have data in each week so as a line chart it will gradient through empty weeks.

              • 4. Re: LOD expression with window function ( to calculate last status before end of week)
                Joshua Milligan

                Ammar,

                 

                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!

                Joshua

                2 of 2 people found this helpful
                • 5. Re: LOD expression with window function ( to calculate last status before end of week)
                  ammar.khwaira

                  Hi Joshua Milligan

                   

                  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).

                   

                  thank you.

                  • 6. Re: LOD expression with window function ( to calculate last status before end of week)
                    Chris Dickson

                    Hi Ammar,

                     

                    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'.

                     

                    EDIT

                    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.

                    2 of 2 people found this helpful
                    • 7. Re: LOD expression with window function ( to calculate last status before end of week)
                      ammar.khwaira

                      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.

                       

                      Thanks !