topic Re: How to transform a stacked chart into a grouped bar chart in QlikView

How to transform a stacked chart into a grouped bar chart

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi guys! How do I transform a stacked chart into a grouped chart even if it has 2 dimensions. I only have 1 expression and I tried to do by simply changing the style at the properties tab however it didn't display the chart I wanted.

This the result I wanted to:

But instead I'm getting this:

Help me on this please. Thanks in advance!

Re: How to transform a stacked chart into a grouped bar chart

techvarun — Mon, 30 Apr 2018 04:44:30 GMT

Can you explain the requirement bit in detail? also try providing some sample data with expected output.

Re: How to transform a stacked chart into a grouped bar chart

Anonymous — Mon, 30 Apr 2018 04:47:20 GMT

I wanted to present my data in a grouped bar chart. The Expression I used is Aggr(Avg([Cost/GSF]),[Account Name],[Cost Item]). The Style of my chart is grouped. I have to present a one colored grouped bar chart but instead I was presented with this:

Whereas expected output is this:

Thanks in advance for your help sir.

Re: How to transform a stacked chart into a grouped bar chart

kaushiknsolanki — Mon, 30 Apr 2018 04:49:37 GMT

Hi,

Hope you went to style tab and changed the subtype from Stacked to Grouped.

Regards,

Kaushik Solanki

Re: How to transform a stacked chart into a grouped bar chart

Anonymous — Mon, 30 Apr 2018 04:50:46 GMT

I already did Sir. I'm still getting a chart which has the colors for all legends. What I wanted is a one colored chart.

Re: How to transform a stacked chart into a grouped bar chart

jonathandienst — Mon, 30 Apr 2018 05:05:26 GMT

Its not very clear what you want. The requirement you describe should have only one dimension and one expression. So just remove the second dimension. Or am I missing something?

Re: How to transform a stacked chart into a grouped bar chart

kaushiknsolanki — Mon, 30 Apr 2018 05:05:40 GMT

Hi,

By Default when you add two dimension it will get spit by color, so that one can easily bifurcate them.

But if you want everything in one color, all you need to do is click on the + sign next to your expression under expression tab.

Here you will find an option to change the background color.

click on it and on right side you can add the color of your choice using any color function like rgb() in definition.

This will assign single color to all of your bars.

Regards,

Kaushik Solanki

Re: How to transform a stacked chart into a grouped bar chart

Anonymous — Mon, 30 Apr 2018 06:45:27 GMT

I have two dimensions for this Sir, the account name and the cost item. I wanted to have a grouped chart which has only one color. I tried removing Account Name as dimension but the chart being displayed had only one account name on the x axis.

Re: How to transform a stacked chart into a grouped bar chart

Anonymous — Mon, 30 Apr 2018 06:47:46 GMT

I see sir, but it's not just one color but the values on the data points should be correct as well, apparently I can't get it right. If I removed any one of the dimensions, the chart turns out to be very different from the expected output.

Re: How to transform a stacked chart into a grouped bar chart

jyothish8807 — Mon, 30 Apr 2018 07:07:06 GMT

Hi Kat,

Is this what you are looking for ?

Br,

Re: How to transform a stacked chart into a grouped bar chart

jonathandienst — Mon, 30 Apr 2018 07:14:35 GMT

OK, so Kaushik's solution is correct as far as the colours are concerned but you see different result in the values at the top of the bar. That's a problem with the expression you have used.

You probably need and Sum(Aggr()..) type expression but if you want specific help, then I suggest that you supply the complete expression, (and define any variables in the expression) and the chart dimensions.

Re: How to transform a stacked chart into a grouped bar chart

Anonymous — Mon, 30 Apr 2018 07:27:25 GMT

Here is my load script sir.

T1:

CrossTable([Cost Item], Data, 6)

LOAD [Account Code],

[Account Name],

[Site Code],

[Site Name],

[Site Year],

[Gross square feet (GSF)],

[JLL Service delivery cost - payroll, taxes, fringe (administration) - R&M],

[Electrical - R&M],

[Elevator - R&M],

[Fire/life safety - R&M],

[General building exterior - R&M],

[General building interior - R&M],

[HVAC - R&M],

[Structural/roofing - R&M],

[Plumbing - R&M],

[Pest control within the building - R&M],

[Miscellaneous / other - R&M]

FROM

(ooxml, embedded labels, table is Data)

Where [Gross square feet (GSF)] > 0 and Len(Trim([Gross square feet (GSF)])) > 0;

T2:

Load

[Account Code],

[Account Name],

[Site Code],

[Site Name],

[Site Year],

[Gross square feet (GSF)],

[Site Name]&[Site Year] as key

Resident T1;

T3:

Load

[Cost Item],

Data/[Gross square feet (GSF)] as "Cost/GSF",

[Site Name]&[Site Year] as key

Resident T1;

Drop Table T1;

Cost Item is the assigned dimension. Those cost items include:

JLL Service delivery cost - payroll, taxes, fringe (administration) - R&M],

[Electrical - R&M],

[Elevator - R&M],

[Fire/life safety - R&M],

[General building exterior - R&M],

[General building interior - R&M],

[HVAC - R&M],

[Structural/roofing - R&M],

[Plumbing - R&M],

[Pest control within the building - R&M],

[Miscellaneous / other - R&M]

The main task is to compute for the Total Average Cost/GSF and Average Cost/GSF (grouped and stacked). Cost are the values of the Cost Items, while GSF is the [Gross square feet (GSF)].

In the Expression tab, the expression I used for the Total Average Cost/GSF is:

Sum(Aggr(Avg([Cost/GSF]),[Account Name],[Site Name],[Site Year]))

while the expression I used for the Average Cost is:

Aggr(Avg([Cost/GSF]),[Account Name],[Cost Item])

Where could I have possibly gone wrong? Although I was able to get the expected output of chart for the Total Average Cost/GSF, the values on the chart were not the same as the expected output as well. As for the Average Cost/GSF Stacked and Grouped, I couldn't get the expected output of both the chart and the values.

Re: How to transform a stacked chart into a grouped bar chart

Anonymous — Mon, 30 Apr 2018 07:28:36 GMT

Sir, I'm running a personal edition so do you mind typing down the script in your reply? Thank you.

Re: How to transform a stacked chart into a grouped bar chart

jonathandienst — Mon, 30 Apr 2018 07:46:35 GMT

The Aggr() expressions must include the chart dimensions and the Average cost expression needs some aggregation function like Sum() or Avg().

Beyond that its hard to day without a small sample qvw that illustrates the problem.

Re: How to transform a stacked chart into a grouped bar chart

kaushiknsolanki — Mon, 30 Apr 2018 08:55:26 GMT

Dear Kat,

As Mr. jontydkpi‌ said, It would be great if you can share some sample data which shows the problem you are facing, without which its difficult to help.

Regards,

Kaushik Solanki

Re: How to transform a stacked chart into a grouped bar chart

palanimurugan — Mon, 30 Apr 2018 09:23:11 GMT

Dear Kat,

You have to change the background color under Expression. Please Refer the below image.

Re: How to transform a stacked chart into a grouped bar chart

Anonymous — Wed, 02 May 2018 01:56:32 GMT

Hi everyone, what I wanted to achieve is this kind of grouped chart:

Instead, I'm getting this chart: