New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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:

 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!

Labels (1)

3 Replies
Partner

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

MVP

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:
If(Match(Person, 'A', 'B'), 'A+B', 'C+D+E+F') as Grouping;
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))`
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:
If(Match(Person, 'A', 'B'), 'A+B', 'C+D+E+F') as Grouping;
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?