1 Reply Latest reply: Jul 7, 2017 5:12 AM by Ravi Balar RSS

    Bar Chart with one dimension using two fields in the expression

    Toby Cameron

      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.