topic Re: stacked and grouped barchart in qlikview in QlikView

stacked and grouped barchart in qlikview

m_kolits — Wed, 28 Mar 2018 07:54:41 GMT

i would like to create a bar chart with three different bar with these expressions:
sum(x)

sum(y)

sum(z)

and with type i want to stacked with A and B

if i add the type field to dimension the qlikview will stack the expressions to one bar. So i dont know how to set the chart.

Can somebody help me?

You can see a data in the attachment and also a picture of what i would like to do

The two types of data came from two different system. I just concatenate in the load script. So i can change it if i have to for his chart.

Thanks for your answer in advance

Marci !

Re: stacked and grouped barchart in qlikview

boorgura — Wed, 28 Mar 2018 14:24:14 GMT

You can try this:

Test:

NoConcatenate

LOAD week,

[2week],

type,

Y as Amount,

'Y' as Flag

FROM

[data.xlsx]

(ooxml, embedded labels, table is Munka1);

Concatenate

LOAD week,

[2week],

type,

X as Amount,

'X' as Flag

FROM

[data.xlsx]

(ooxml, embedded labels, table is Munka1);

Concatenate

LOAD week,

[2week],

type,

Z as Amount,

'Z' as Flag

FROM

[data.xlsx]

(ooxml, embedded labels, table is Munka1);

Concatenate

LOAD week,

[2week],

type,

paid as Amount,

'paid' as Flag

FROM

[data.xlsx]

(ooxml, embedded labels, table is Munka1);

Basically transposing the data.

Then use 2week, Flag and type as dimensions.

And,

sum({<Flag = {"X", "Y", "Z"}>} Amount) --> as your expression.

Re: stacked and grouped barchart in qlikview

MarcoWedel — Wed, 28 Mar 2018 21:32:49 GMT

Hi,

one front end solution could be:

The already proposed data model change however seems the cleaner approach, given it makes sense to combine X, Y, Z into a common value field and a separate name field to distinguish their origin.

A short way to do so would be a Crosstable load.

hope this helps

regards

Marco

Re: stacked and grouped barchart in qlikview

m_kolits — Thu, 29 Mar 2018 08:07:32 GMT

Thank you Guys!

All sollution is good. My fault, but i forgot to write one thing. I would like to put the "paid" to a linechart to this barchart. The paid dimension will be on the right side. Like in excel with secondary dimension.

With your sollution, there is only one expression and if i add the new one for the paid the grouping and stacking didnt work. Could you help me this problem?

Re: stacked and grouped barchart in qlikview

boorgura — Thu, 29 Mar 2018 16:52:08 GMT

I don't think we can directly achieve that - meaning stacked, grouped bars and lines in the same chart.

One way is to super-impose another chart on top of the current object, and make the top object transparent.

However, this has many limitations - hovering only works on the top chart, and axis sizing and object docking should be manually adjusted to align with each other.

Re: stacked and grouped barchart in qlikview

MarcoWedel — Sun, 01 Apr 2018 19:55:28 GMT

Glad it worked.

Please close your thread if your initial question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

Re: stacked and grouped barchart in qlikview

m_kolits — Wed, 04 Apr 2018 08:36:11 GMT

Oh i see.

Thank your answer!

Re: stacked and grouped barchart in qlikview

PSatQlikForums — Mon, 25 Mar 2019 12:48:52 GMT

Hi Marco,

I have a similar requirement, and so far, your solution is closest to what I need.

There are some key differences, which I struggle to implement though:

1. I only need X and Y

2. X and Y have 4 and 5 distinct attribute values (which I want to stack in the two separate bars X and Y)

3. I do not have the type dimension. Instead, I want to stack the different values of X and Y.

In the end, I want my bar chart to look exactly like yours, except that I have two bars for each date, and instead of type A and B, I will have 4 colors stacked in bar X, and 5 colors stacked in bar Y.

I would greatly appreciate any help.

Thanks in advance!

Sincerely,
Peter

Re: stacked and grouped barchart in qlikview

asanoop24 — Tue, 14 Jan 2020 17:49:54 GMT

Is this type of visual possible in Qlik Sense as well? Currently, as per my understanding, Qlik Sense doesn't accept more than two dimensions in a bar chart to make it grouped and stacked simultaneously. Is there a way around for this in Qlik Sense too?