Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Bar Chart with one dimension using two fields in the expression

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.

1 Solution

Accepted Solutions
balar025
Creator III
Creator III

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))

View solution in original post

1 Reply
balar025
Creator III
Creator III

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))