Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
chris_soto
New Contributor

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

4 Replies
bharathadde
Contributor II

Re: Combine multiple dimensions into one on bar graph

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
New Contributor

Re: Combine multiple dimensions into one on bar graph

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
New Contributor III

Re: Combine multiple dimensions into one on bar graph

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

Partner
Partner

Re: Combine multiple dimensions into one on bar graph

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]
;