Stacked bar charts - custom aggregation

[Qlik_Newbie]

Hi All, 

I'm having trouble with a stacked bar graph in Qlik. 

Let's say I have the following data:

Round	Person	Revenue
1	A	1
1	B	2
1	C	3
1	D	4
1	E	5
1	F	6
2	A	1
2	B	2
2	C	3
2	D	4
2	E	5
2	F	6

Is there a way to create a stacked bar chart showing the following:

1) Round number along the X axis (i.e. a bar for Round 1 and a bar for Round 2) 

2) Each bar showing aggregated revenue for A+B and C+D+E+F? 

I'm struggling to explain point 2) clearly, but the resulting bars should each be split into 2 colours with values of 3 (14%) and 18 (86%) for A+B and C+D+E+F respectively.

I'm doing everything in Qlik cloud, if that's useful to know.

Any help will be much appreciated!

[uacg0009]

Hi,

What you want to do is:

1.stacked bar chart

2.show values and percentage for every Round.

I think maybe you need to use extension to achieve that.

Or if you grouped bar chart is also fine for you, I think you can show the values and percentage using Qliksense standard chart.

Aiolos Zhao

[sunny_talwar]

I have attached a sample with two approaches you can take

1) With script modifications - Create a new field called Grouping based on Person

Table:
LOAD *,
	 If(Match(Person, 'A', 'B'), 'A+B', 'C+D+E+F') as Grouping;
LOAD * INLINE [
    Round, Person, Revenue
    1, A, 1
    1, B, 2
    1, C, 3
    1, D, 4
    1, E, 5
    1, F, 6
    2, A, 1
    2, B, 2
    2, C, 3
    2, D, 4
    2, E, 5
    2, F, 6
];

and now use Round and Grouping as your dimensions

2) Use ValueList function as a dimension in your bar chart

Dimensions

Round
ValueList(1, 2)

Expression

Pick(ValueList(1, 2), Sum({<Person = {'A', 'B'}>}Revenue), Sum({<Person = {'C', 'D', 'E', 'F'}>}Revenue))

[Qlik_Newbie]

---

@sunny_talwar wrote:

I have attached a sample with two approaches you can take

1) With script modifications - Create a new field called Grouping based on Person

Table:
LOAD *,
	 If(Match(Person, 'A', 'B'), 'A+B', 'C+D+E+F') as Grouping;
LOAD * INLINE [
    Round, Person, Revenue
    1, A, 1
    1, B, 2
    1, C, 3
    1, D, 4
    1, E, 5
    1, F, 6
    2, A, 1
    2, B, 2
    2, C, 3
    2, D, 4
    2, E, 5
    2, F, 6
];

and now use Round and Grouping as your dimensions

2) Use ValueList function as a dimension in your bar chart

Dimensions

Round
ValueList(1, 2)

Expression

Pick(ValueList(1, 2), Sum({<Person = {'A', 'B'}>}Revenue), Sum({<Person = {'C', 'D', 'E', 'F'}>}Revenue))

---

Hi,

Thanks so much for your help. The outputs are exactly what I need. Problem is my actual data set is a lot larger with a lot more rows (100,000+) containing say 1000 distinct values rather than just 1 and 2. Is there an alternative to the valuelist function that will do the same job?