Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
m_kolits
New Contributor II

stacked and grouped barchart in qlikview

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 !

1 Solution

Accepted Solutions

Re: stacked and grouped barchart in qlikview

Hi,

one front end solution could be:

QlikCommunity_Thread_296328_Pic1.JPG

QlikCommunity_Thread_296328_Pic2.JPG

QlikCommunity_Thread_296328_Pic3.JPG

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

7 Replies
boorgura
Valued Contributor

Re: stacked and grouped barchart in qlikview

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

Hi,

one front end solution could be:

QlikCommunity_Thread_296328_Pic1.JPG

QlikCommunity_Thread_296328_Pic2.JPG

QlikCommunity_Thread_296328_Pic3.JPG

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

m_kolits
New Contributor II

Re: stacked and grouped barchart in qlikview

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?

boorgura
Valued Contributor

Re: stacked and grouped barchart in qlikview

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

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

m_kolits
New Contributor II

Re: stacked and grouped barchart in qlikview

Oh i see.

Thank your answer!

PSatQlikForums
New Contributor

Re: stacked and grouped barchart in qlikview

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