Get the data based on the Filter of Aggregated data

[#1]

Hi All,

I want to get the Result 2 from the below Data.  The below Information is exactly same in the attached excel. Please refer the excel, if u feel free. Could you please guide me , as this is little bit urgent requirement.

Data:   

Test Month	Month	SKU	Pen_Id	Start Wt	OOI	Ink per page
T008QVY	Sept	Gen 3 Pyramid Color - Corbett X	C5347910	38.151	28.395	0.030971
T008QVY	Sept	Gen 3 Pyramid Color - Corbett X	C5347908	38.28	28.918	0.026748
T008QVY	Sept	Gen 3 Pyramid Color - Corbett X	C5347904	39.262	29.351	0.030033
T008QVY	Sept	Gen 3 Pyramid Color - Corbett X	C5347909	39.266	28.667	0.031173
T008QVY	Sept	Gen 3 Pyramid Color - Corbett X	C5347907	39.389	29.466	0.030069
T008RQY	Sept	Gen 3 Pyramid Color - Corbett X	C5348461	39.44	28.69	0.031617
T008RQY	Sept	Gen 3 Pyramid Color - Corbett X	C5348467	39.441	29.681	0.030984
T008R8Y	Sept	Gen 3 Pyramid Color - Corbett X	C5348139	39.533	28.989	0.030562
T008QVY	Sept	Gen 3 Pyramid Color - Corbett X	C5347905	39.542	28.801	0.030427
T008R8Y	Sept	Gen 3 Pyramid Color - Corbett X	C5348141	39.615	29.492	0.029773
T008R8Y	Sept	Gen 3 Pyramid Color - Corbett X	C5348140	39.629	29.507	0.031631
T008R8Y	Sept	Gen 3 Pyramid Color - Corbett X	C5348138	39.643	29.234	0.030614
T008QVY	Sept	Gen 3 Pyramid Color - Corbett X	C5347906	39.674	29.246	0.030761
T008RQY	Sept	Gen 3 Pyramid Color - Corbett X	C5348464	39.696	29.618	0.031009
T008QVY	Sept	Gen 3 Pyramid Color - Corbett X	C5347903	39.72	29.371	0.030438
T008S3Y	Oct	Gen 3 Pyramid Color - Corbett X	C5348753	39.3	29.218	0.031605
T008SPY	Oct	Gen 3 Pyramid Color - Corbett X	C5349261	39.338	29.238	0.030149
T008SPY	Oct	Gen 3 Pyramid Color - Corbett X	C5349263	39.344	29.099	0.031045
T008S3Y	Oct	Gen 3 Pyramid Color - Corbett X	C5348751	39.367	29.31	0.030944
T008SPY	Oct	Gen 3 Pyramid Color - Corbett X	C5349264	39.401	29.182	0.030504
T008S3Y	Oct	Gen 3 Pyramid Color - Corbett X	C5348752	39.407	29.203	0.031987
T008SPY	Oct	Gen 3 Pyramid Color - Corbett X	C5349265	39.44	29.416	0.030938
T008SPY	Oct	Gen 3 Pyramid Color - Corbett X	C5349262	39.517	29.602	0.030045
T008SPY	Oct	Gen 3 Pyramid Color - Corbett X	C5349266	39.586	29.618	0.03115
T008SPY	Oct	Gen 3 Pyramid Color - Corbett X	C5349260	39.642	29.562	0.031111
T008VWY	Nov	Gen 3 Pyramid Color - Corbett X	C5530577	39.054	28.84	0.031427
T008VWY	Nov	Gen 3 Pyramid Color - Corbett X	C5530581	39.206	29.353	0.03079
T008VGY	Nov	Gen 3 Pyramid Color - Corbett X	C5530269	39.208	28.975	0.030546
T008VGY	Nov	Gen 3 Pyramid Color - Corbett X	C5530270	39.217	29.473	0.031432
T008VGY	Nov	Gen 3 Pyramid Color - Corbett X	C5530268	39.236	29.203	0.032364
T008VGY	Nov	Gen 3 Pyramid Color - Corbett X	C5530264	39.284	29.723	0.030841
T008VGY	Nov	Gen 3 Pyramid Color - Corbett X	C5530265	39.293	28.989	0.031224
T008VWY	Nov	Gen 3 Pyramid Color - Corbett X	C5530576	39.309	29.02	0.031464
T008VGY	Nov	Gen 3 Pyramid Color - Corbett X	C5530267	39.326	29.388	0.030672
T008VGY	Nov	Gen 3 Pyramid Color - Corbett X	C5530266	39.339	29.349	0.031218
T008VWY	Nov	Gen 3 Pyramid Color - Corbett X	C5530580	39.373	29.564	0.031641
T008VGY	Nov	Gen 3 Pyramid Color - Corbett X	C5530262	39.448	29.249	0.030906
T008VWY	Nov	Gen 3 Pyramid Color - Corbett X	C5530579	39.478	29.461	0.030354
T008VWY	Nov	Gen 3 Pyramid Color - Corbett X	C5530578	39.53	28.3	0.003074
T008VGY	Nov	Gen 3 Pyramid Color - Corbett X	C5530263	39.616	29.762	0.03032
For the Below Cycle 1 Calculations, we are considering all the above data)
Cycle 1
Result1:		Start Wt	OOI	Ink per page
	Avergae Wt	39.357	29.238	0.030
	Std Dev	0.311744116	0.346506328	0.004528129
	+3 sig	40.29225799	30.27759398	0.04369313
	- 3 Sig	38.42179329	28.19855602	0.016524357
	Max	39.72	29.762	0.032364
	Min	38.151	28.3	0.003074
	Data Check	Exclude Data	Data Good	Exclude Data
AFTER CYCLE 1 total 3 Pens Excluded  for Cycle 2( 1st row,2nd row and 40th row Pens are excluded which are marked in Red for our Understanding that we are filtering these data for below Aggregate Calculations for Cycle 2). Formula  for e.g.  for calculating the Average Wt. =AVERAGE(E4:E39,E41:E41) , they are filtering the 3 rows from the source data. On what Reference we are Filtering the Data: Value --> ( Each column individual value should be compared with its corresponding  (-3 Sig , +3 Sig  and Min, Max) as seen below: If  (( Value  > -3 Sig and Value < +3 Sig) and (Value > Min and Value < Max)) from Result1 , then we consider as GOOD Data else we filter the data. e.g  for Column OOI:  If ( ( OOI > (-3 Sig  of OOI)  and OOI  <  (+3 Sig of OOI) ) and (OOI > (Min of OOI)  and OOI  < (Max of OOI)) from Result1 then consider only that data for Cycle 2 .. Like the same , it repeats for other columns (Start Wt, Ink per page)
Cycle 2
Result2		Start Wt	OOI	Ink per page
Result2: (In Result2 , We have to get the Aggregations (Average Wt,Std Dev,etc…)  by filtering the Data based on the ( Value  > -3 Sig and Values < +3 Sig) and (Value > Min and Value < Max) from Result1	Avergae Wt	39.421	29.295	0.031
	Std Dev	0.163637972	0.282354305	0.000586125
	+3 sig	39.91196797	30.14192778	0.032671024
	- 3 Sig	38.93014014	28.44780195	0.029154274
	Max	39.72	29.762	0.032364
	Min	39.054	28.667	0.029773
	Data Check	Data Good	Data Good	Data Good

Could you please send an answer for this post to email id: harvarinf1531@gmail.com

[muthukumar77]

Hi,

Refer this app.

I hope it will work.

PFA

[sunny_talwar]

Using the qvd created by muthukumar‌‌, I tried t0 further expand to complete Cycle 1 and get the Cycle2:

[#4]

Dear Sunny,

That's what exactly I want. Thanks for your great help. But your great

help always be impressed.

Thanks a lot Muthu for your response in getting Cycle1.

Regards,

Harish

[sunny_talwar]

Great!! Would you be able to close this thread down by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny