Expression gives wrong results

[Cathalc]

Hi all,

I've made an expression to calculate the average of the three month rolling sum over the past twelve months. But I have some issues with it. This is my table with values and some intermediate results (Middle table):

I use the following expression: 

avg( 1000*(aggr( (RangeSum(above(total count({<date={"$(='>=' & date(MonthStart(Max(date), -13)) & '<' & date(MonthStart(Max(date))))"}>} DefectType),0,3)))/
((RangeSum(above(total sum({<date={"$(='>=' & date(MonthStart(Max(date), -13)) & '<' & date(MonthStart(Max(date))))"}>} FHTime),0,3)))/60), month) ))

As you can see, I try to calculate the average of the three month rolling sum over the past twelve months of the values in the second column divided by the values in the third column. My result is only slightly off, but it's important I get a perfect result. The correct value is 180.98 while the answer i get is 177.2. 

Does anyone see a mistake in my expression? I'm new to Qlik so I probably missed something obvious to others.

Thank you!

Cathal

[sunny_talwar]

Would you be able to share a sample to check this out?

[Cathalc]

I'm guessing you mean a sample of the data?

I'm getting my data directly from the company server so best I can do is give you the following table of original data:

FHKey	DefKey	ACName	FHTime (minutes)	DefectType	day	year	month	date	=sum(FHTime)	ATA	count(DefectType)
-	43564,34245853OY-HHV	OY-HHV	-	MAREP	9	2019	apr.	09/04/2019	0	74	1
08/04/2019OO-NHD	-	OO-NHD	92	-	8	2019	apr.	08/04/2019	92	-	0
-	43563,269176123OO-NSH	OO-NSH	-	MAREP	8	2019	apr.	08/04/2019	0	62	1
-	43563,661337465PH-NHV	PH-NHV	-	MAREP	8	2019	apr.	08/04/2019	0	28	1
-	43563,670678322TU-HNE	TU-HNE	-	PIREP	8	2019	apr.	08/04/2019	0	79	1
-	43563,720344063OO-NSQ	OO-NSQ	-	MAREP	8	2019	apr.	08/04/2019	0	62	1
-	43563,797983715OO-NSF	OO-NSF	-	MAREP	8	2019	apr.	08/04/2019	0	62	1
-	43563,807737384OO-NSF	OO-NSF	-	MAREP	8	2019	apr.	08/04/2019	0	64	1
-	43563,6704403949G-NHH	9G-NHH	-	MAREP	8	2019	apr.	08/04/2019	0	34	1
07/04/2019OO-NHA	-	OO-NHA	19	-	7	2019	apr.	07/04/2019	19	-	0
07/04/2019OO-NSF	-	OO-NSF	96	-	7	2019	apr.	07/04/2019	96	-	0
07/04/2019OO-NSL	-	OO-NSL	25	-	7	2019	apr.	07/04/2019	25	-	0
07/04/2019OO-NSM	-	OO-NSM	15	-	7	2019	apr.	07/04/2019	15	-	0
07/04/2019OO-NSM	-	OO-NSM	27	-	7	2019	apr.	07/04/2019	27	-	0
07/04/2019OO-NSM	-	OO-NSM	70	-	7	2019	apr.	07/04/2019	70	-	0
07/04/2019OO-NSZ	-	OO-NSZ	54	-	7	2019	apr.	07/04/2019	54	-	0
07/04/2019OY-HHV	-	OY-HHV	44	-	7	2019	apr.	07/04/2019	44	-	0
07/04/2019OY-HHV	-	OY-HHV	101	-	7	2019	apr.	07/04/2019	101	-	0
07/04/2019OY-HLV	-	OY-HLV	114	-	7	2019	apr.	07/04/2019	114	-	0
07/04/2019PH-NHU	-	PH-NHU	51	-	7	2019	apr.	07/04/2019	51	-	0
-	43562,44925691OY-HVR	OY-HVR	-	MAREP	7	2019	apr.	07/04/2019	0	11	1
-	43562,424640509OO-NSR	OO-NSR	-	MAREP	7	2019	apr.	07/04/2019	0	72	1
-	43562,468705058OO-NSZ	OO-NSZ	-	MAREP	7	2019	apr.	07/04/2019	0	63	1
-	43562,479707604PH-NHV	PH-NHV	-	MAREP	7	2019	apr.	07/04/2019	0	28	1
-	43562,651205752PH-NHV	PH-NHV	-	MAREP	7	2019	apr.	07/04/2019	0	64	1
06/04/2019OO-NHA	-	OO-NHA	27	-	6	2019	apr.	06/04/2019	27	-	0
06/04/2019OO-NHA	-	OO-NHA	37	-	6	2019	apr.	06/04/2019	37	-	0
06/04/2019OO-NHA	-	OO-NHA	59	-	6	2019	apr.	06/04/2019	59	-	0
06/04/2019OO-NSL	-	OO-NSL	21	-	6	2019	apr.	06/04/2019	21	-	0
06/04/2019OO-NSL	-	OO-NSL	73	-	6	2019	apr.	06/04/2019	73	-	0
06/04/2019OO-NSM	-	OO-NSM	16	-	6	2019	apr.	06/04/2019	16	-	0
06/04/2019OO-NSM	-	OO-NSM	18	-	6	2019	apr.	06/04/2019	36	-	0
06/04/2019OO-NSM	-	OO-NSM	29	-	6	2019	apr.	06/04/2019	29	-	0
06/04/2019OO-NSM	-	OO-NSM	33	-	6	2019	apr.	06/04/2019	33	-	0
06/04/2019OO-NSM	-	OO-NSM	51	-	6	2019	apr.	06/04/2019	51	-	0
06/04/2019OO-NSQ	-	OO-NSQ	38	-	6	2019	apr.	06/04/2019	38	-	0
06/04/2019OO-NSQ	-	OO-NSQ	86	-	6	2019	apr.	06/04/2019	86	-	0
06/04/2019OO-NSQ	-	OO-NSQ	91	-	6	2019	apr.	06/04/2019	91	-	0

Let me know if you meant something else 🙂