Quarter totals issue

[Anonymous]

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

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

[vvira1316]

HI,

Isn't this same as Subtract Dates?

[Anonymous]

Hi Vijay,

Not exactly same,  now it involves bit more complexity what ever Nicole did is correct but i didn't provide complete data set/requirement  first time. Thanks,

[Nicole-Smith · Accepted solution]

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.

[Anonymous]

Hi Nicole,

I’m sorry to trouble you, I was able to fix  all the issues in my app except one. Please can you help me.

In the attached qvw and excel for the product “ REB”  it’s not showing any data for Q12014 and Q22014. I think it’s due to the InQuarter function, I tried to play around with that but still didn’t work. Please help me. Thank you so much  for your help,

Harini

[Nicole-Smith]

I changed the load script and the chart dimension/expression.  Your example file is attached with my changes.

[Anonymous]

Thank you so much Nicole. Really appreciate it.