# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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