1 2 3 4 Previous Next 54 Replies Latest reply on Oct 1, 2014 3:01 PM by Chandni Bhowmick Go to original post
      • 30. Re: Rolling backlog calculation
        kettan

        The best for me would be to wait (2 weeks) till I have access to a laptop with Tableau Desktop.

         

        That said, I wonder if this query (line 1 to 5 from before plus an inserted line 2) works:

         

        SELECT d.*, l.*
        ,  dateadd('d', 1 - day(d.[VALID_INTIL]), d.[VALID_INTIL]) AS [month trunc]
        FROM [Sheet 1$] d, [lookup$] l
        WHERE d.[VALID_FROM] <= l.[month end]
        and iif(d.[VALID_INTIL] is null, #1/1/2099#, d.[VALID_INTIL])>= l.[month start]
        and l.[month start] <= date()
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        

         

        This will tell if the dateadd calculation is okay or not and thus lead us one step closer to completion.

        • 31. Re: Rolling backlog calculation
          Chandni Bhowmick

          The code is not giving any error but the result still remains the same

          • 32. Re: Rolling backlog calculation
            Chandni Bhowmick

            Hi

             

               Hope you're doing great. I'm still kinda stuck with this. Let me know when you get access to Tableau Desktop.
            Thank you so much.

             

            Chandni

            • 33. Re: Rolling backlog calculation
              kettan

              Thanks for telling. I expect to have access again tomorrow and hope to take a look at your workbook in this week.

              • 34. Re: Rolling backlog calculation
                kettan

                Finally I am back home :-)

                 

                This query works:

                 

                SELECT *
                FROM [data$] d, [lookup$] l
                WHERE d.[VALID_FROM] <= l.[month end]
                and iif(d.[VALID_INTIL] is null, #1/1/2099#, d.[VALID_INTIL]) >= l.[month start]
                and l.[month start] <= date()
                and not (
                  dateadd("d", 1 - day(iif(d.[VALID_INTIL] is null, #1/1/2099#, d.[VALID_INTIL])), iif(d.[VALID_INTIL] is null, #1/1/2099#, d.[VALID_INTIL])) = l.[month start]
                  and d.[VALID_INTIL] < l.[month end]
                  )
                
                
                

                 

                The issue was that the formula in line 7 had zero tolerance for null dates.

                Therefore I copied the iif in line 4 and pasted it twice to the dateadd function.

                That helped.

                 

                See also attached workbook.

                .

                • 35. Re: Rolling backlog calculation
                  Chandni Bhowmick

                  Hi

                   

                    I was getting the same output but the output isn't correct. Please see the attached excel file for the expected output.

                   

                  Thanks!

                  • 36. Re: Rolling backlog calculation
                    kettan

                    The bug was not in the SQL code, but in the lookup data sheet.

                    Month Start should be the first of each month, but were all in January.

                    I have attached a new Tableau workbook (plus the corrected Excel file).

                    Hopefully everything works as it should now  :-)

                    • 37. Re: Rolling backlog calculation
                      Chandni Bhowmick

                      Hi

                       

                      In Jan the total should be 27 since 27 issues were open in Jan. Similarly  the total for Feb and March should be 50 and 52 respectively( shown in the excel sheet attached previously). Why are the numbers different in your tableau workbook?

                       

                      Thanks

                      • 38. Re: Rolling backlog calculation
                        kettan

                        I have changed the formula back to basics:

                         

                        SELECT *
                        FROM [data$] d, [lookup$] l
                        WHERE d.[VALID_FROM] <= l.[month end]
                        and iif(d.[VALID_INTIL] is null, #1/1/2099#, d.[VALID_INTIL]) >= l.[month start]
                        and l.[month start] <= date()
                        
                        
                        
                        
                        

                         

                        The above mentioned query returns:

                         

                        Jan 29 (27)

                        Feb 61 (50)

                        Mar 79 (52)

                         

                        For starters, could you tell me what two (29-27=2) internal issue numbers are too much in Sheet 3 in attached workbook in January?

                        .

                        • 39. Re: Rolling backlog calculation
                          Chandni Bhowmick

                          I'm not sure I understand your question.

                          • 40. Re: Rolling backlog calculation
                            Santosh Shettar

                            Hi,

                             

                            I was going through your post. I found it interesting. Kettan has given you the right solution.

                            As per your requirement I understand that you need to show number of backlog issues by month.

                             

                            So to meet your requirement I have attached the modified workbook here. Hope this is the solution you were looking for.

                             

                            Thanks.

                            Santosh

                            1 of 1 people found this helpful
                            • 41. Re: Rolling backlog calculation
                              kettan

                              I'm not sure I understand your question.

                              What I meant is if you could go through Sheet 1 in attached workbook and share which internal issue numbers are wrongly included in January (28 vs 27), February (56 vs 50), and March (61 vs 52).

                               

                              As for January, I think your result sheet is 28, not 27. It is Excel that calculates wrong in your sum formula by excluding adjacent rows or something like that.

                               

                              As for February, I noticed that your result sheet excludes 6 internal issue numbers beginning in January which have no VALID_INTIL date. I assume these should be included because internal issue numbers having a VALID_INTIL date are included in your result sheet even if they begin in January.

                               

                              As you noticed, my number of rows have changed again.

                              This is because I made some minor adjustments in the query:

                               

                              SELECT *
                              FROM [data$] d, [lookup$] l
                              WHERE datevalue(d.[VALID_FROM]) <= l.[month end]
                              and iif(d.[VALID_INTIL] is null, #1/1/2099#, datevalue(d.[VALID_INTIL])) >= l.[month start]
                              and l.[month start] <= date()
                              and (
                                d.[VALID_INTIL] is null or
                                not (year(d.[VALID_INTIL]) = year(l.[month start]) and month(d.[VALID_INTIL]) = month(l.[month start]))
                                )
                              
                              
                              
                              
                              
                              
                              
                              
                              
                              

                               

                              Line 1 to 4 is the basic technique explained in  The Cross Join Collection

                              The function datevalue removes the time part from VALID_FROM and VALID_INTIL.

                              I had to either truncate the time part or include time 23:59:59 in the lookup column month end.

                              I chose to truncate. If there are performance issues, the other option might perform better.

                               

                              Line 4 uses IIF to give VALID_INTIL null values a needed dummy future date.

                              Line 5 ensures that these dummy future dates don't create future months.

                               

                              Line 6 to 9 exclude issue numbers which are closed in the same month as the shown month.

                              Line 7 ensures that null end dates are not excluded.

                              In this regard, it is important to notice its usage of the or operator instead of the usual and.

                              Line 8 excludes issue numbers which are closed in the same month as the shown month.

                              Here it is worth to notice the not ( condition 1 and condition 2 ) structure which only excludes if both conditions are true.

                              .

                              • 42. Re: Rolling backlog calculation
                                kettan

                                Welcome to the forum, Santosh, and thanks for helping.

                                • 43. Re: Rolling backlog calculation
                                  Chandni Bhowmick

                                  "As for February, I noticed that your result sheet excludes 6 internal issue numbers beginning in January which have no VALID_INTIL date. I assume these should be included because internal issue numbers having a VALID_INTIL date are included in your result sheet even if they begin in January."  

                                   

                                  You're right! I missed this point! Thank you so much!

                                  • 44. Re: Rolling backlog calculation
                                    kettan

                                    You are welcome :-)