Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
It’s simple problem, but hard to explain.
Let’s just say I have a table:
Date | Aircraft | Cycles | MEL |
01-01-2017 | VH-ABC | 5 | 25-01 (1) |
01-01-2017 | VH-DEF | 6 | |
02-01-2017 | VH-ABC | 7 | |
02-01-2017 | VH-DEF | 5 | |
03-01-2017 | VH-ABC | 6 | |
03-01-2017 | VH-DEF | 6 | 25-01 (1) |
03-01-2017 | VH-DEF | 6 | 25-01 (1) |
04-01-2017 | VH-ABC | 5 | |
04-01-2017 | VH-DEF | 6 | 38-03 |
05-01-2017 | VH-ABC | 7 | |
05-01-2017 | VH-DEF | 4 |
So, 25-01 (1) appears three times, 38-03 appears once and 32-12 (2) doesn’t appear at all.
The number of MEL possibilities is like over 50. But in this example lets just use 3. 25-01 (1) was used twice in the same day on the same aircraft and once on another day, 38-03 was used once and I made up a number (32-12 (2)) that doesn’t appear in the table as an example.
The formula is: MEL Rate = Number of MELs x 1000 / Number of Total Cycles
So, the MEL Rate for 25-01 (1); would be 3 x 1000 / 57 (The sum of cycles by distinct days, i.e. not double counting the 6 cycles for VH-DEF on 03-01-2017)
This MEL Rate equals 52.63….etc
I’m trying to graph like attached.
My issue is when I do the expression in the chart visualisation, I’m doing Count(MEL)*1000/Sum(Cycles). This fails though because there is no correlation between MELs and Cycles. Even when I use Aggr to group the MELs and Cycles for distinct days etc, I always end up with a situation where because not every day has the same MEL, it cant include the cycles on days when that MEL wasn’t used in the calculation.
I have keys for dates and cycles and keys for dates and MELs but I cant make a key that includes dates, aircrafts, MELs and cycles all in one…or can I? I need to include all cycles (as per filter settings), and count all MELs of a certain type and chart like the attached.
Cheers.
Hi,
Use below expression.
Count(MEL)*1000/Sum(Total Aggr(if(Aggr(Count(Aircraft),Aircraft,Date)>=2,Min(Cycles,Date),sum(Distinct Cycles)),Date,Aircraft))
Hi,
Use below expression.
Count(MEL)*1000/Sum(Total Aggr(if(Aggr(Count(Aircraft),Aircraft,Date)>=2,Min(Cycles,Date),sum(Distinct Cycles)),Date,Aircraft))