Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chris_soto
Contributor II
Contributor II

Combine multiple dimensions into one on bar graph

I am trying to sum the sales of dealers by product lines they carry, the only issue is the product lines are spread out between 3 different fields. A product line can be in the Product Line 1 field for one dealer, and in Product Line 2 field for another dealer.  I'm trying to use the Product lines as a dimension in my chart and the yearly sales as my measures. 

 

Here is an example layout of the data. Any dealer who has Apples as a product, I want to combine combine their sales into one bar on my chart. Any dealer who has Bananas, I want their sales to be combined into another bar. It's just to basically see how our Apples dealers are doing, how our Banana dealers are doing, etc sales wise.

Data Example.PNG

Labels (3)
4 Replies
bharathadde
Creator II
Creator II

Please follow below Process

Data:
LOAD * Inline
[
Dealer,Product Line 1,Product Line 2,Product Line 3,2019 Sales
Bills Store, Apples,Bananas,Carroes,1000
Joes Store, Bananas,Apples,Grapes,2000
Dereks Store, Apples, Grapes, Pretzels,3200
Jims Store, Oranges, Apples, Peaches, 1500
];

NewTable:
LOAD
Dealer,
[Product Line 1] as Product
Resident Data;
Concatenate(NewTable)

LOAD
Dealer,
[Product Line 2] as Product
Resident Data;
Concatenate(NewTable)

LOAD
Dealer,
[Product Line 3] as Product
Resident Data;

chris_soto
Contributor II
Contributor II
Author

Thank you for your response, however I'm not sure this will work for me because in my live data I have thousands of dealers that are always being added and deleted, along with changing sales dollars. Is there something that would work for this?

saurabh_karlewar
Contributor III
Contributor III

Try data transpose using cross table load to get all the products in single column.

jatishqv
Partner - Contributor III
Partner - Contributor III

Hi

I think Saurabh's CrossTable approach is the best way forward.

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes...

 

Capture.PNG

Data:
LOAD
    *
FROM [lib://Data_Store/Sales_Data.xlsx]
(ooxml, embedded labels, table is Sales_Data);


Product:
CrossTable ([Product Line_Temp],[Product Line],1)
LOAD 
	Dealer,
    [Product Line 1],
    [Product Line 2],
    [Product Line 3],
    [Product Line 4]
Resident Data;


Drop Fields 
	[Product Line 1],
    [Product Line 2],
    [Product Line 3],
    [Product Line 4],
    [Product Line_Temp]
;