How to create stacked bar chart with column headers as dimensions and calculated data points as expressions ?

[#1]

Hi All,

I am new to QlikView and learning & experimenting with bar charts. I am stuck with the following use case which I am trying out. Any pointers you can provide on how I can achieve the below will be helpful.

The Data

I have an excel with the columns of Customer data - header names like Name,Address,City,State,Country. I have created additional columns, which, for each of the row is populated with a YES or NO based on whether the Customer data attribute is correct or not. Please see the attached excel.

Data representation

Need to create a single stacked bar chart. The dimensions will be Name,Address,City,State,Country. For a given dimension e.g. Name the stacked bar chart will have two parts - the correct name and the incorrect name. Similarly the next line in the bar chart will be for Address. So when a user views the bar chart, he should be quickly able to visualize the number of correct names and incorrect names and when he clicks on correct names, the data rows at the bottom should be populated with only those rows. Similarly when he clicks on the incorrect names he should be easily taken to those rows so he can see which are the incorrect entries.

Challenge

1) Presently able to use the concept of expressions without dimensions and show either of incorrect or correct data count. However with only an expression if the user clicks on the bar chart the data rows are not refreshed. If I show the incorrect data count on the bar chart, when the user clicks the chart on Address bar for incorrect entries, the data rows below should be refreshed with only those  records having incorrect address.

2) Not able to create stacked bar chart with each dimension having two parts -correct/in-correct

Is the above possible in QlikView; if so how ?

Thanks in advance for your help,

[jonathandienst]

Hi

The easiest way is to transform that date a little. See the attached

HTH

Jonathan

[jonathandienst · Accepted solution]

Script:

Data:

LOAD ID,

    NAME,

    ADDRESS,

    CITY,

    STATE,

    POSTCODE,

    COUNTRY,

    IS_NAME_CORRECT,

    IS_ADDRESS_CORRECT,

    IS_CITY_CORRECT,

    IS_STATE_CORRECT,

    IS_POSTCODE_CORRECT,

    IS_COUNTRY_CORRECT

FROM TRIAL.xlsx

(ooxml, embedded labels);

Fields:

CrossTable(Field, Value, 1)

LOAD ID,

  ADDRESS,

  CITY,

  STATE,

  POSTCODE,

  COUNTRY

Resident Data;

Status:

CrossTable(Field, State, 1)

LOAD ID,

  IS_NAME_CORRECT As NAME,

  IS_ADDRESS_CORRECT As ADDRESS,

  IS_CITY_CORRECT As CITY,

  IS_STATE_CORRECT As STATE,

  IS_POSTCODE_CORRECT As POSTCODE,

  IS_COUNTRY_CORRECT As COUNTRY

Resident Data;

Join(Fields)

LOAD * Resident Status;

DROP Table Status;

Chart dimensions: Field, State

Chart expression: =Count(distinct ID)

Set style to stacked and sort order to Text(Z->A)

[#4]

Hi,

I can't open your QVW because of personnel edition. You can have a look on it.

With Regards

Harshita Gaur

[#5]

Hi Johathan,

Thanks a lot! this is what i was looking for and the cross table concept  is interesting.

I wanted to check with you on how to expand on this concept. In the cross table, I want to add one more column, say isComplete which in the excel is organised in the same way as isCorrect with seperate columns for each attribute.

    IS_NAME_CORRECT,

    IS_ADDRESS_CORRECT,

    IS_CITY_CORRECT,

    IS_STATE_CORRECT,

    IS_POSTCODE_CORRECT,

    IS_COUNTRY_CORRECT

    IS_NAME_COMPLETE,

    IS_ADDRESS_COMPLETE,

    IS_CITY_COMPLETE,

    IS_STATE_COMPLETE,

    IS_POSTCODE_COMPLETE,

    IS_COUNTRY_COMPLETE

Can you please share with me an example on this line. I tried on similar lines trying to add one more table similar to Status and use the Join, but I guess I am missing something.

Thanks in advance !

[#6]

Hi Harshita

Thanks a ton !

I was able to use this option and also expand on it to add new columns, this creates the table similar to the cross table construct though a bit manual, it is very easy to understand and consume.

Thank you for sharing your idea.

[jonathandienst]

You could add another cross table load for those fields, like this:

Complete:

CrossTable(Field, Complete, 1)

LOAD ID,

  IS_NAME_COMPLETE As NAME,

  IS_ADDRESS_COMPLETE As ADDRESS,

  IS_CITY_COMPLETE As CITY,

  IS_STATE_COMPLETE As STATE,

  IS_POSTCODE_COMPLETE As POSTCODE,

  IS_COUNTRY_COMPLETE As COUNTRY

Resident Data;

Join(Fields)

LOAD * Resident Complete;

DROP Table Complete;

This will add a [Comple] field to the Fields table.

[#8]

Hi Jonathan

Thanks for the reply. I tried, however still only one field is showing up in the Table Viewer.

Pasting the modification I checked on your code, can you check what may be wrong and advice,

Thanks in advance,

Data:

LOAD ID,

     NAME,

     ADDRESS,

     CITY,

     STATE,

     POSTCODE,

     COUNTRY,

     IS_NAME_COMPLETE,

     IS_ADDRESS_COMPLETE,

     IS_CITY_COMPLETE,

     IS_STATE_COMPLETE,

     IS_POSTCODE_COMPLETE,

     IS_COUNTRY_COMPLETE,

     IS_NAME_CORRECT,

     IS_ADDRESS_CORRECT,

     IS_CITY_CORRECT,

     IS_STATE_CORRECT,

     IS_POSTCODE_CORRECT,

     IS_COUNTRY_CORRECT

FROM TRIAL.xlsx

(ooxml, embedded labels);

Fields:

CrossTable(Field, Value, 1)

LOAD ID,

    ADDRESS,

    CITY,

    STATE,

    POSTCODE,

    COUNTRY

Resident Data;

Complete:

CrossTable(Field, Complete, 1)

LOAD ID,

    IS_NAME_COMPLETE As NAME,

    IS_ADDRESS_COMPLETE As ADDRESS,

    IS_CITY_COMPLETE As CITY,

    IS_STATE_COMPLETE As STATE,

    IS_POSTCODE_COMPLETE As POSTCODE,

    IS_COUNTRY_COMPLETE As COUNTRY

Resident Data;

Correct:

CrossTable(Field, Correct, 1)

LOAD ID,

    IS_NAME_CORRECT As NAME,

    IS_ADDRESS_CORRECT As ADDRESS,

    IS_CITY_CORRECT As CITY,

    IS_STATE_CORRECT As STATE,

    IS_POSTCODE_CORRECT As POSTCODE,

    IS_COUNTRY_CORRECT As COUNTRY

Resident Data;

Join(Fields)

LOAD * Resident Complete;

LOAD * Resident Correct;

DROP Table Complete;

DROP Table Correct;

[jonathandienst]

The join only applies to the first load:

Join(Fields)

LOAD * Resident Complete;

LOAD * Resident Correct;

You need

Join(Fields)

LOAD * Resident Complete;

Join(Fields)

LOAD * Resident Correct;

[#10]

Thank you. Works perfectly