Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tcameron
New Contributor

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
Contributor III

Re: Bar Chart with one dimension using two fields in the expression

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

1 Reply
balar025
Contributor III

Re: Bar Chart with one dimension using two fields in the expression

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

Community Browser