Working with categorical columns in Qlik sense.

[Nithin212]

Hi All,

Could someone please advise me on how to do the below mentioned one in Qlik sense .

- I have a table with 10 columns in that two columns (column1 , column 2 ) are having 10 categories each  and both columns are having same categories.
And there is one more column called sales   which contains sales numbers. Also, I have created a variable input drop down box which contains values like name, year, and location.
How can I create a table in Qlik sense with first column as drop-down box values and then horizontally two headers with Column 1 and Column 2 and then under that 10 categories and for each category how can I do summation of sales.

Thanks in advance 🙂

[Anil_Babu_Samineni]

This is confusing, can you please do the workshop in Excel and share it with us, so that we can see in Qlik?

[Nithin212]

Thanks, Anil Babu, for the reply. Please find the snippet containing input data and the required output. Please let me know if any other information is required.

[diegozecchini]

Hi!
if I understood correctly try to load Data with Categories and Sales, check that your dataset is structured properly, with Column1, Column2..N and Sales.

Then create a var for the drop-down

Navigate to Variable Overview, and create a new variable, lets say "vSelection" and assign values: name, year, location in a drop-down.
Next we add a variable input object

Go to Custom Objects > Qlik Dashboad Bundle > Variable Input and we set it up:
Variable: vSelection
Values: name, year, location
Display Type: Dropdown

Go to Charts → Pivot Table, Drag & Drop Dimensions:
Rows: =$(vSelection) (Dynamic Column based on Drop-down)
Columns: Column1 and Column2 and all colums you want
Sub-columns: Categories (Ensure Column1 and Column2 contain the same categories)
Measure: Sum(Sales)
Format the Table and the expected outcome would be:

Selection Value | Category (Col1) | Category (Col2) | Sales Total
Year                   |            A             |            A             |   5000
                          |            A             |            B             |   3000
Year                   |            B             |            A             |   4000
                          |            B             |             B            |   6000

[Nithin212]

Thank you for providing the reply @diegozecchini  in Qlik Can I have under Category (col1) all the categories horizontally? and then sum below that something like the attached snippet?

[Anil_Babu_Samineni]

I guess it is feasible, do you mind sharing excel file for first screenshot. 

[Nithin212]

Hi @Anil_Babu_Samineni , Please find it attached!! Thank you .

[Anil_Babu_Samineni]

Like this?

Script been used:

CrossTable([Customer Review], [Review Type], 3)
LOAD [Name of Manufacturer],
[Production Year],
Location,
Customer_review_1,
Customer_review_2
FROM
[QlikCommunity\Copy of Input_temp.xlsx]
(ooxml, embedded labels, table is [Input Data], filters(
Remove(Col, Pos(Top, 6))
));

CrossTable(Measure, Sales, 5)
LOAD [Name of Manufacturer],
[Production Year],
Location,
Customer_review_1,
Customer_review_2,
Sales
FROM
[QlikCommunity\Copy of Input_temp.xlsx]
(ooxml, embedded labels, table is [Input Data]);

[Nithin212]

Yes, thank you. But is it possible to add a drop-down for variable input so that I can choose to see the year or location and sum of sales instead of the name?