Depicting a ratio measure in a bar chart

[andreasort]

Dear all,

I have created a ratio measure which I like to depict in a bar chart; meaning the ratio shall be shown per month (dimension). Although it seems to work fine with one month, it does no longer as soon as I select two or more month. The ratio is still calculated on the total basis instead of the basis per month.

Here are the details:

Imagine I have a list of records. Let us assume each record has the same set of data fields, one of them nominating a month, the rest whatever you may imagine.

I like to count the number of data fields which have a specific value; e.g. "X".  That count shall go across all records.

Now, I like to depict the ratio of the amount of "X" per month.

What I did:

I defined a variable which counts all my records (across all countries and months). I am also able to count the occurrences of the value "X".

Then I created a bar chart. The measure was number of occurrences of "X" divided by the number of records (the variable). The dimension was the month.

The bar chart worked fine as long as I selected one month. As soon as I select two or more month, the ration was calculated always in a way taking all the records in that selected period. But the bar chart never made a correct split per month.

So for instance:

Month     data a     data b     data c

Jan          X               fgss     zturz

Jan          fdhg          X          trzew

Jan          gfztr          tzrt        nbv

Jan          trwz          X            etr

Feb          trz             zter        X

Feb          X              uze       gfg

So the overall number of records is 6 (the variable). The total number of "X" is 5.

The ratio of #"X" per records in January is 2/4. For February it is 2/2.

What I like to see in the barchart while selecting Jan and Feb in my general selection in Qlik Sense is

- one bar for January showing 0,5 and

- one bar for February showing 1.

What my chart is actually showing me is

- one bar for January showing 3/6 (correct by coincidence) and

- one bar for February showing 2/6

because the chart always takes the total amount of records and not the number of record per month.

Here is the formular of the measure I use (actually I am counting blanks ...):

(

sum((isNull()))+

sum((isNull()))+

sum((isNull())))

* -1 / ($(vNbOfRecords))

)

I have been advised already that set analysis may help but I simply do not understand how to apply that.

Could anyone support please?

Thanks in advance,

kind regards,

Andreas

[Lisa_P]

Hi Andreas,

I have taken your data and created a bar chart.  See progression below with calculations in tables before converting to bar chart.

For your scenario you can use your formula to count nulls.

[andreasort]

Dear Lisa,

thanks for fast reply! I like to follow up in the thread but I still have difficulties to find it again. Until then – thanks again – I still need to digest and understand what you have done.

Kind regards,

Andreas

[andreasort]

Dear Lisa!

I am trying to understand your reply.

You mentioned that I can keep my formula to count the nulls. So no change needed for

"

(

sum((isNull()))+

sum((isNull()))+

sum((isNull())))

* -1 / ($(vNbOfRecords))

)"

Then you mention to count the month: "Count(Month)". I guess you mean to count number of rows *per* month. But that is exactly what I do not know how to do. My variable $(vNbOfRecords) is defined as: =Count([Job Reference]) where Job Reference is an arbitrary identifier. So why is the genral period selection in Qlik Sense not working for this?

Certainly you can still help me.

Thanks in advance,

kind regards,

Andreas

[Lisa_P]

Hi Andreas,

I was using your sample data to do the calculations.

My count(Month) was giving me something to divide by which gave the number of rows and the chart then also had the month as dimension.

Can you give me some sample data more representative of what you are trying to do ?

Regards,

Lisa

[andreasort]

Hello Lisa,

find attached some more screenshots and some sample data (hope the come through). The “month” is derived from last column (H) in the spread sheet.

The charts tell you the number of records (declarations, counting unique “job references”). The formula is as mentioned above. It also shows the count of blanks. The August picture shows how the bar chart is defined (edit view).

In August: 496 records, 28 blanks thus a ratio of 28/496 = 0,0564.

In July: 73 records, 1 blank => ratio is 1/73 = 0,0137

For July and August I like to see exactly these two values in my bar chart. However, as you can see in the “july and august chart.jpg” picture, the July and the August ratio are calculated on the total sum of records (496+73=569, but due to duplicates it is calculated as 567 in the chart); meaning for July the ratio is depicted as 1/567 = 0,0017 and for August it is depicted as 28/567=0,0494.

Will that help?

Greetings,

Andreas