7 Replies Latest reply: Sep 20, 2017 7:51 PM by Harini Konka RSS

    Quarter totals issue

    Harini Konka

      Hi All,

      I’m trying to calculate the number of days it took from “already completed” type to “will complete” type. It’s calculating the number of Days correct but when I try to plot this data cross quarters it’s not calculating it correctly (it’s showing the same value across all the quarters). Please can someone help me with this issue. Attached qvw and excel sample data for reference.

      To make it clear:

      In the attached excel and qvw for ID:434 the number of days it took from already completed to will complete (10/8/2015-2/11/2016)= 126 days-  this is correct

      But when I plot it in the bar chart it’s showing Q42015-126, Q12016-126, Q22016-126- Wrong

      Expected Result:  for Q42015-84

                                           Q12016-41

                                            Q2016- 0

        • Re: Quarter totals issue
          Nicole Smith

          This load script should do the trick:

           

          Tmp:
          LOAD ID,
          [Approval date]
          RESIDENT Main;
          
          LEFT JOIN (Tmp)
          LOAD ID,
          MIN([will complete Date]) AS [min will complete date],
          MIN([already completed Date]) AS [min already completed date]
          RESIDENT Main
          GROUP BY ID;
          
          LEFT JOIN (Main)
          LOAD ID,
          [Approval date],
          IF(InQuarter([min will complete date], [Approval date], 0) AND InQuarter([min already completed date], [Approval date], 0), [min will complete date] - [min already completed date],
          IF (InQuarter([min will complete date], [Approval date], 0), [min will complete date] - QuarterStart([Approval date]),
          IF (InQuarter([min already completed date], [Approval date], 0), DayStart(QuarterEnd([Approval date])) - [min already completed date]))) AS Days
          RESIDENT Tmp;
          
          DROP TABLE Tmp;
          

           

          I've also attached the same example from your original post with this code included and working.

            • Re: Quarter totals issue
              Vijay Vira

              HI,

               

              Isn't this same as Subtract Dates?

              • Re: Quarter totals issue
                Nicole Smith

                You already have it taking Type into account in your load into Main (lines 14 and 15):

                 

                Main:
                LOAD ID,
                     Product,
                     [Line Item],
                     Type,
                     Status,
                     [Approval date],
                     [sales rep],
                     day([Approval date]) as Day,
                     month ([Approval date]) as Month,
                     Year ([Approval date]) as year,
                     IF(Len([Approval date])>0, 'Q' & ceil( month(date([Approval date])) / 3)) as quarter,
                     IF(Len([Approval date])>0, 'Q' & ceil( month(date([Approval date])) / 3)) & Year ([Approval date]) as Quarter_Year,
                     if([Type]='already completed',[Approval date]) as [already completed Date],
                     if([Type]='will complete',[Approval date]) as [will complete Date]     
                FROM Test2.xlsx (ooxml, embedded labels, table is Sheet1);
                
                

                 

                So I think all you need is to add where clauses in the Tmp table for Status and Type (lines 5-6 and 13):

                 

                Tmp: 
                LOAD ID, 
                     [Approval date] 
                RESIDENT Main
                WHERE Status='Approved'
                     AND MATCH([Type], 'already completed', 'will complete'); 
                
                LEFT JOIN (Tmp) 
                LOAD ID, 
                     MIN([will complete Date]) AS [min will complete date], 
                     MIN([already completed Date]) AS [min already completed date] 
                RESIDENT Main
                WHERE Status='Approved' 
                GROUP BY ID; 
                
                LEFT JOIN (Main) 
                LOAD ID, 
                     [Approval date], 
                     IF(InQuarter([min will complete date], [Approval date], 0) AND InQuarter([min already completed date], [Approval date], 0), [min will complete date] - [min already completed date], 
                          IF(InQuarter([min will complete date], [Approval date], 0), [min will complete date] - QuarterStart([Approval date]), 
                          IF(InQuarter([min already completed date], [Approval date], 0), DayStart(QuarterEnd([Approval date])) - [min already completed date]))) AS Days 
                RESIDENT Tmp; 
                
                DROP TABLE Tmp;
                
                

                 

                I'm unable to test this with the "apple" example that you're referencing, as there is no "apple" data in your example data.