Combining yes/no to bar charts

[#1]

Hello,

I'm very new to Qlik and am still exploring the possibilities.

We collect data from measurements from machines and we need to visualise that.

We were quickly able to make graphs from the numeric data (temperatures and pressures of machines), but that is not enough.

One of the things that we need is combining data from yes/no questions.

For example, a daily question for a machine might be if there are any alarms.

Because we have multiple questions that are answered with yes/no, we need to have a bar chart that represents all (20 or 30) yes/no questions and display it as a single bar, split into all 'good' values combined and all 'bad' values combined.

The user should be able to click on the 'bad' part of the graph and see details of the questions/items that are not in a good condition or have any alarms.

Btw: it's not necessary to have it as a bar chart.It might not be necessary to show the 'good' values if that would make things easier as the end user is only interested where things go wrong.

I hope my explanation makes sense and more so, I hope that any of you can help me with this.

Thanks in advance,

Erik

[sunny_talwar]

There should be couple of ways to do this.

1) Add Yes/No field as your second dimension and it should automatically divide the count into yes and no.

2) Use two expressions with one for yes and one for no using set analysis.

[rupamjyotidas]

Use stalwar1‌ suggestions with a Stacked option

[johnw]

If your data model looks like this:

Inspections:
Machine 
Date 
Alarms?
Oil dripping?
Malfunctioning lights?

That doesn't lend itself at all to what you need. You need to convert this to a different structure, such as by using a crosstable load.

Inspections:
ID
Machine
Date

Questions:
ID
Question
Answer

Now you can do what stalwar1 said to make the actual graph. But the assumption in those solutions is that you have the second data model, not the first.

[#5]

First of all, thank you all for answering.

John is correct, our data model is like the first example, but somewhat larger of course (over 150 lines/data items).

The current structure is like this:

Date - date/time field

Oil pressure machine 1 -  number field

Oil temperature machine 1 - number field

Cooling water level machine 1 - number field

Alarms machine 1 - yes/no field

Oil level machine 1 - yes/no field (i.e. too low/good/too high)

etc etc

I can easily make a graph of the temperatures so the user can inspect for unusual changes or trends.

The problem is the yes/no. I surely hope there is another way to make the (bar) chart then changing the structure of the questions. As I understand it now, I need to split the original Excel into two seperate parts.

In a way it makes sense as the structure and types of data is different, but I'm still hoping there's another solution.

[johnw]

Data model changes tend to be my preferred way of addressing problems. My main reason for that is that I prefer the complexity be in my script rather than in my objects. My secondary reason is that data model solutions tend to perform better than other options.

So yes, I'm recommending you take the single table from Excel, and break it into two separate tables in QlikView. One for basic information about, well, whatever each row in your Excel table is intended to represent. And one table for at least the Y/N questions, and possibly all questions, depending on where it is more useful to you to have your other questions.

Can it be done without data model changes? Oh, probably. If I were brute forcing it, I guess I'd create a Y expression and an N expression. Each expression would be designed to add up all Y or N answers. Maybe something like these using my version of the data model for consistency in my answers:

sum(substringcount([Alarms?]&[Oil dripping?]&[Malfunctioning lights?],'Y'))
sum(substringcount([Alarms?]&[Oil dripping?]&[Malfunctioning lights?],'N'))

But with 20 or 30 or 150 Y/N questions, and presumably the desire to add more in the future, that could get unweildy. Fortunately, there's a clear pattern here to how we add the fields. The trick is recognizing questions that are Y/N. In our shop, "all" Y/N questions have a label that ends with a question mark, as in the labels above. So in our shop, I could identify the fields by looking for the question mark. With that in mind, I might be able to do something like this:

sum(substringcount([$(=concat(if([$Table]='Inspections' and right([$Field],1)='?',[$Field]),']&['))],'Y')
sum(substringcount([$(=concat(if([$Table]='Inspections' and right([$Field],1)='?',[$Field]),']&['))],'N')

So we're using dollar sign expansion and concat() to build our field list insted of writing it out manually. Completely untested, and probably syntax errors, and maybe there's a much easier way, but that's the first thing that came to mind after data model changes. I don't know if you have any way of identifying the fields of interest outside of listing them all. But maybe if not, the brute force listing them all like the earlier two expressions would be OK in your case.

Still, either way, I'd probably just make data model changes.

[#7]

I think I will split the data into numeric fields and the (binary) yes/no fields and then work on your suggestions.

However I've runto other problems now (for which I will open another discussion)

Thanks and I'll keep you informed.

[ramasaisaksoft]

Hi Erik,

As per understanding Butterfly chart/Pivot table will help to you to show in a very good manner.

Butterfly chart example

Expressionless and Dimensionless Charts | Qlikview Cookbook

if i miss understood your requirement please show me correct way to understand in a pictorial manner.