Product life cycle report

[geam1988]

Hallo, i have to create a report about product life cycle. i have attched sample.

i have to create quarter from the difference bitween end.date - start.date.  for example,  if the difference is 990 days then it will be in quarter 11,  if difference is 720 days then it will be quarter 8, if difference is 700 days then it will be quarter 7.  i want to have maximum 12 quarter.

formula for  quartale is the difference between   end.date - start.date  divided by 90. like this,  num(date(end.date ) ) - num(date(start.date)) / 90. or any suggestion ?

any suggestion about how to create product life cycle report.

Please see the attached document. i have attached data file und .QVW file.  Please do not hesitate to contact me if you have any question.

Best Regards, Kazi

[sunny_talwar]

Can you try with this script

Production:
LOAD KEY, 
     Product, 
     KEY & '_' & Product as %KEY_Production_Defect, 
     [production date],
     date([production date]) as start.date,
     QuarterName ([production date]) as Quarter.Poduction,
     Pick(Ceil(Month([production date])/3),'Q1','Q2','Q3','Q4') & '-' & right(Year([production date]),2) as Quarter.Poduction_ERDAT,
     Quantity 
FROM
[isoch.xlsx]
(ooxml, embedded labels, table is Tabelle1);


Defect:
LOAD Defect.KEY, 
     Defect.Product, 
     Defect.KEY & '_' & Defect.Product as %KEY_Production_Defect, 
     Defect.date, 
     QuarterName (Defect.date) as Quarter.Defect,
     Pick(Ceil(Month(Defect.date)/3),'Q1','Q2','Q3','Q4') & '-' & right(Year(Defect.date),2) as Quarter.Defect_ERDAT,
     Defect.Quantity
FROM
[isoch.xlsx]
(ooxml, embedded labels, table is Tabelle2);

left join(Production)
load Distinct 
     %KEY_Production_Defect, 
     Defect.KEY, 
     Defect.Product, 
     Defect.date, 
     date(Defect.date) as end.date,
     QuarterName (Defect.date) as Quarter.Defect,
     Pick(Ceil(Month(Defect.date)/3),'Q1','Q2','Q3','Q4') & '-' & right(Year(Defect.date),2) as Quarter.Defect_ERDAT,
     Defect.Quantity
Resident Defect;

FinalProduction:
LOAD *,
	 Pick(Ceil((IterNo())/90),'Q1','Q2','Q3','Q4') as test_Q,
	 IterNo() as Days,
	 Date(start.date + IterNo() - 1) as date
Resident Production
While start.date + iterno ()- 1 <= end.date;

DROP Tables Defect, Production;

[geam1988]

Hallo Sunny,

thanks for your reply. there is one problem. if you take start date 01.04.2018 and end. date 10.12.2018, difference is 254  days and  it should show only  Q3 as test_Q. But it shows Q1, Q2 and Q3.  How can i make it to show only one test_Q, just Q3.

Best Regards, Kazi

[sunny_talwar]

So, you only want to see the max quarter based on total number of days? I thought you wanted to see Q1 for 1-90 days and then Q2 for 91-180 days and so on .... but it seems that if the total number of days are less than 90, you want to see Q1 and if they are less than 180, but greater than 91... you want to see just Q2? Is that right?

[geam1988]

Hi Sunny,

yes, i want to show max quarter.

 you only want to see the max quarter based on total number of days?  Yes

if the total number of days are less than 90, you want to see Q1 and if they are less than 180, but greater than 91... you want to see just Q2? Is that right?  Yes

Best regards, Kazi

[sunny_talwar · Accepted solution]

Then try this

Production:
LOAD KEY, 
     Product, 
     KEY & '_' & Product as %KEY_Production_Defect, 
     [production date],
     date([production date]) as start.date,
     QuarterName ([production date]) as Quarter.Poduction,
     Pick(Ceil(Month([production date])/3),'Q1','Q2','Q3','Q4') & '-' & right(Year([production date]),2) as Quarter.Poduction_ERDAT,
     Quantity 
FROM
[isoch.xlsx]
(ooxml, embedded labels, table is Tabelle1);


Defect:
LOAD Defect.KEY, 
     Defect.Product, 
     Defect.KEY & '_' & Defect.Product as %KEY_Production_Defect, 
     Defect.date, 
     QuarterName (Defect.date) as Quarter.Defect,
     Pick(Ceil(Month(Defect.date)/3),'Q1','Q2','Q3','Q4') & '-' & right(Year(Defect.date),2) as Quarter.Defect_ERDAT,
     Defect.Quantity
FROM
[isoch.xlsx]
(ooxml, embedded labels, table is Tabelle2);

left join(Production)
load Distinct 
     %KEY_Production_Defect, 
     Defect.KEY, 
     Defect.Product, 
     Defect.date, 
     date(Defect.date) as end.date,
     QuarterName (Defect.date) as Quarter.Defect,
     Pick(Ceil(Month(Defect.date)/3),'Q1','Q2','Q3','Q4') & '-' & right(Year(Defect.date),2) as Quarter.Defect_ERDAT,
     Defect.Quantity
Resident Defect;

FinalProduction:
LOAD *,
	 end.date - start.date + 1 as NoOfDays,
	 Pick(Ceil((end.date - start.date + 1)/90),'Q1','Q2','Q3','Q4') as test_Q
Resident Production;

DROP Tables Defect, Production;