Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Qlik_Newbie
New Contributor

Stacked bar charts - custom aggregation

Hi All, 

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

Let's say I have the following data:

RoundPersonRevenue
1A1
1B2
1C3
1D4
1E5
1F6
2A1
2B2
2C3
2D4
2E5
2F6

 

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!

Labels (1)
3 Replies
uacg0009
Valued Contributor

Re: Stacked bar charts - custom aggregation

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

Re: Stacked bar charts - custom aggregation

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
New Contributor

Re: Stacked bar charts - custom aggregation


@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?