Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Would you be able to share a sample to check this out?
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 🙂