Multiple columns to form a dimension in a column chart

[#1]

Hi,

I'm completely new to Qlik Sense, and having trouble displaying data in a column chart. I have a feeling that my data source (Excel spreadsheet) is not structured in an appropriate way. Otherwise, maybe I just don't know how to set it up.

My data looks like:

ID	Status	A	B	C
1	Open	Impacted	Impacted
2	Open		Impacted
3	Open	Impacted		Impacted
4	Closed			Impacted

I want to create a column chart which shows A, B, C along the x-asis and the Count of 'Open' IDs that belong to the A, B, C.

So it should appear like:

A = 2

B = 2

C = 1

Is there a way to do this using the data I have?

[#2]

Note that the real data contains more than A, B, C and that all can be 'Impacted' and it is not limited to only 2 (as my example might seem to show)

[Miguel_Angel_Baeyens · Accepted solution]

Although for me this is more a QlikView question than a Qlik Sense question, here is my guess:

You can create a load script like this (from memory, check help if it does not work the first time):

CROSSTABLE (Item, Impacted, 2) LOAD ID,
Status,
A,
B,
C
FROM File.xlsx;

Then a measure for your chart like this with a set analysis syntax

Count({< Status = {'Open'}, Impacted = {'Impacted'} >} Item)

Dimension will be "Item".

Miguel

[elif-tutuk]

Let's say it is a Qlik question and you can do this analysis on both products as they use the same Qlik Engine:-).

Thanks a lot!

Elif.

[#5]

Thanks heaps! Took me a while to understand the syntax but it makes sense now!

It mentioned something about a synthetic key being created. Is this always normal with crosstables?

Anyway, will mark this is answered. Cheers