5 Replies Latest reply on Nov 29, 2016 7:42 AM by Michel Caissie

    LOD using include is failing to get maximum date in the week

    ammar.khwaira

      Hi

       

      I have two weeks , starting on Monday. i want for each week to get the issues which have its last status in the week as 'open'.

       

      in the attached data file, Issue B is open at the end of Week 1, and Issues C and D are open at the end of Week2.

       

       

      I tried to create a LOD expression which will include the issue number along with the Week no in the view to get the maximum status date, then filter on it, but it failed

      to get the correct data as it keeps showing issue A in Week1.

       

       

      What i am missing here ?  Is filtering on LOD expression possible ?

       

       

      Thanks

        • 1. Re: LOD using include is failing to get maximum date in the week
          Michel Caissie

          Ammar,

           

          The Include function takes into account what you have on the filter shelf. So since you only keep the Open status

          { INCLUDE [Issue]: max([status change Date]) returns the max date of the 'Open' status rows by Issue.

           

          What you need is the FIXED function, which is independant of the filter shelf. So

          { FIXED [Issue]: max([status change Date]) will return the max date by Issue  whatever the status or other dimension on the filter shelf.

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: LOD using include is failing to get maximum date in the week
            ammar.khwaira

            Hi Michel

             

             

            Thanks for your reply. 

             

             

            Will the  Fixed function  take into consideration the Week number when calculating the max(status_date) ? i understand it will ignore the status from the filter shelf, but i need to get the last date for each issue even if the issue spans two or more weeks.

             

             

            for example: if issue B had a status = 'open' in Week3 , and we are now checking Week2 data,  i want Tableau to get the max(status_date) for issue B in Week2 (ignoring Week3 in this case).  Is that possible?

            • 3. Re: LOD using include is failing to get maximum date in the week
              Michel Caissie

              Ammar,

               

              I am not sure to understand your requirements. Can you tell me what would be the expected results for the following scenario ?

               

              Issue X

              Week1  status change Date   In progress

              Week2  no status change Date

              Week3 status change Date    Open

               

               

              What do you want to see in the report for each week

              Week 1 do you want to count X

              Week2  do you want to count X

              Week3  do you want to count X

              • 4. Re: LOD using include is failing to get maximum date in the week
                ammar.khwaira

                Hi Michel

                 

                What i wanted is that " In Each week, count all issues that have their LAST status in that week = 'Open'.

                 

                Now, the workbook is showing :

                Week1, Count = 1    ( cause:  Issue B ended as Open at the end of Week 1)

                Week2, Count = 2    ( cause :  Issues C and D ended as Open at the end of Week 2)

                 

                This is correct.

                 

                But, If we add another row for Issue B to the data set (like the attached Excel) so that it will be in Week 3 and in Closed status, Fixed function will now ignore showing issue B in Week 1 and the result will be wrong :

                 

                Week 1  will not show at all.   This is not correct  (cause:  Fixed function took the last status of Issue B as Closed , as it was Closed in Week 3.  But it should have shown Issue B here as it was Open at the end of Week 1).   

                Week2, Count = 2   ( cause :  Issues C and D ended as Open at the end of Week 2).  This is correct

                 

                 

                What i have done to make this work is that i edited the calculated field to be :

                 

                Last_date =>    { INCLUDE  [Issue]: max([status change Date])}

                 

                and created a new calculate field to count the open issues at the end of each week:

                 

                Open Issues =>    if [Status] = 'Open' and [status change Date] = [last date] then [Issue] end

                 

                and used this Open_issues field on the columns shelf.

                 

                 

                Thanks.

                • 5. Re: LOD using include is failing to get maximum date in the week
                  Michel Caissie

                  ok  I see.

                   

                  Glad you found a solution.

                   

                  Another way to do it,  if you want to compute a lod on a per Week basis,  is to bring the Week part of your date dimension in the Lod.

                  For example, to compute the last status change Date  per Issue per Week , you would have;

                  {FIXED [Issue],DATETRUNC('week', [status change Date] ): max([status change Date])}

                   

                  And to compute the last Open status change Date per Issue per Week , you would have;

                  {FIXED [Issue],DATETRUNC('week', [status change Date] ): max(if [Status] = 'Open' then [status change Date] end)}

                   

                  And you could keep the Issues you want with a filter like;

                  [Last  Date by week] = [Last Open Date by week] and [status change Date] = [Last Open Date by week]

                   

                  In your case your solution is simplier, but since  FIXED lod is independant of the dimension in the view, it gives more reusability in case you need the same numbers in different views.

                   

                  Michel

                  1 of 1 people found this helpful