topic Re: Subtotals and multiple dimensions in a barchart in QlikView

Subtotals and multiple dimensions in a barchart

Wed, 25 Nov 2020 16:16:04 GMT

Hello ,

I have a requirement where I have to create a bar chart for comparing the zone wise sale of the product , I have data in the below format :

Data:
Load * Inline [
Zone,ProdType,Sales1
North,Beer,200
North,SD,100
South,Beer,250
South,SD,80
];

I need to create a bar chart in such a way that the subtotals are visible and a concatenated zone and product type also comes as a bar for each dimension(North/South) ,

attaching a sample .

In the bar chart (required format),a 3rd bar having sum for beer and SD sale for each zone should be visible and should come as the first bar ,this bar's label can be North for NorthBeer and NorthSD .

Re: Subtotals and multiple dimensions in a barchart

Mon, 18 Jul 2016 04:05:50 GMT

Hello , please help or suggest any solution for this problem .

Re: Subtotals and multiple dimensions in a barchart

sunny_talwar — Mon, 18 Jul 2016 09:25:58 GMT

Is this what you are looking for?

Re: Subtotals and multiple dimensions in a barchart

sunny_talwar — Mon, 18 Jul 2016 09:32:45 GMT

Or are you looking for something like this?

Re: Subtotals and multiple dimensions in a barchart

Mon, 18 Jul 2016 09:32:57 GMT

Hello Sunny ,

Thanks for the reply , I am looking for a third bar chart for each zone/prodtype combo, which contains the total for NorthBeer and NorthSD and same for SouthBeer and SouthSD .

Something like this :

Total

TotalBeer

TotalSD

North

NorthBeer

NorthSD

South

SouthBeer

SouthSD

Hope this helps .

Re: Subtotals and multiple dimensions in a barchart

sunny_talwar — Mon, 18 Jul 2016 10:16:04 GMT

Look at my other response below... does that help?

Re: Subtotals and multiple dimensions in a barchart

Mon, 18 Jul 2016 10:21:41 GMT

Hi Sunny ,

This is very helpful but I need one more functionality where I get a similar kind of sub-division in total bar too(which must be the first bar) , is that possible ?

Re: Subtotals and multiple dimensions in a barchart

sunny_talwar — Mon, 18 Jul 2016 10:58:14 GMT

So move the total bar to the top?

Used a sorting expression:

=Match(Pick(Dim, Zone&ProdType, Zone, 'Total'), 'Total')

Re: Subtotals and multiple dimensions in a barchart

Mon, 18 Jul 2016 11:45:56 GMT

Hi Sunny ,

I might be confusing you but I want the total bar to be subdivided into 2 more bars for beer and SD :

Total

TotalBeer

TotalSD

Re: Subtotals and multiple dimensions in a barchart

sunny_talwar — Mon, 18 Jul 2016 12:29:02 GMT

This?

Re: Subtotals and multiple dimensions in a barchart

sunny_talwar — Mon, 18 Jul 2016 12:31:43 GMT

Or this:

Re: Subtotals and multiple dimensions in a barchart

Mon, 18 Jul 2016 12:45:24 GMT

Hi Sunny ,

The requirement is to display the Total bar separately instead of staked ,something like the below format :

Total

TotalBeer

TotalSD

Similar to below which you have already done for zone wise division .

Re: Subtotals and multiple dimensions in a barchart

sunny_talwar — Mon, 18 Jul 2016 12:48:31 GMT

Alright, lets try this one more time

Re: Subtotals and multiple dimensions in a barchart

Mon, 18 Jul 2016 12:53:34 GMT

Thanks for all the help Sunny , really appreciate the time you have taken to solve the problem

Re: Subtotals and multiple dimensions in a barchart

Tue, 19 Jul 2016 09:18:20 GMT

Hi Sunny ,Sorry for bugging you again , I am unable to arrange the chart in a specific order like :

Total

Beer

North

NorthBeer

NorthSD

but in the qvw which you have shared ,SD is coming between NorthSD and SouthSD , can you suggest a way that shifts the SD bar to the desired location (which is 3rd from top in this case).

Re: Subtotals and multiple dimensions in a barchart

sunny_talwar — Tue, 19 Jul 2016 09:52:57 GMT

Used this expression for sorting:

=Match(Pick(Dim, Zone&ProdType, Zone, ProdType, 'Total'), 'Total', 'Beer', 'SD', 'North', 'NorthBeer', 'NorthSD', 'South', 'SouthBeer', 'SouthSD')

Re: Subtotals and multiple dimensions in a barchart

Tue, 19 Jul 2016 10:01:48 GMT

Thanks a lot .

Is there anyway to make this dynamic , like if I want to add more zones like east , west .

Re: Subtotals and multiple dimensions in a barchart

sunny_talwar — Tue, 19 Jul 2016 10:27:24 GMT

There might be a way, but it might be a little challenging. Will have to explore it a little

Re: Subtotals and multiple dimensions in a barchart

Tue, 19 Jul 2016 10:42:05 GMT

If its challenging for you then it is nearly impossible for me , please explore if you have some spare time . TIA .

Re: Subtotals and multiple dimensions in a barchart

sunny_talwar — Tue, 19 Jul 2016 10:47:39 GMT

My friend, don't under-estimate your potential. I am sure if you spend enough time to get this done, you will end up finding a better solution than what I may propose because you have more data and information then I do.

Having said that, I will still try to look for a way to get this done.

Best,

Sunny