topic Re: Combine multiple dimensions into one on bar graph in App Development

Combine multiple dimensions into one on bar graph

chris_soto — Sat, 16 Nov 2024 20:19:45 GMT

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.

Re: Combine multiple dimensions into one on bar graph

bharathadde — Fri, 02 Aug 2019 17:22:59 GMT

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;

Re: Combine multiple dimensions into one on bar graph

chris_soto — Mon, 05 Aug 2019 13:33:35 GMT

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?

Re: Combine multiple dimensions into one on bar graph

saurabh_karlewar — Mon, 05 Aug 2019 14:07:16 GMT

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

Re: Combine multiple dimensions into one on bar graph

jatishqv — Tue, 06 Aug 2019 15:34:20 GMT

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/crosstable.htm

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