It’s simple problem, but hard to explain.
Let’s just say I have a table:
Date  Aircraft  Cycles  MEL 
01012017  VHABC  5  2501 (1) 
01012017  VHDEF  6 

02012017  VHABC  7 

02012017  VHDEF  5 

03012017  VHABC  6 

03012017  VHDEF  6  2501 (1) 
03012017  VHDEF  6  2501 (1) 
04012017  VHABC  5 

04012017  VHDEF  6  3803 
05012017  VHABC  7 

05012017  VHDEF  4 

So, 2501 (1) appears three times, 3803 appears once and 3212 (2) doesn’t appear at all.
The number of MEL possibilities is like over 50. But in this example lets just use 3. 2501 (1) was used twice in the same day on the same aircraft and once on another day, 3803 was used once and I made up a number (3212 (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 2501 (1); would be 3 x 1000 / 57 (The sum of cycles by distinct days, i.e. not double counting the 6 cycles for VHDEF on 03012017)
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))