Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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)
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
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.
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