Two dimensions, side by side, filtered - Qlik Community

Beginner · ‎2018-11-21

Hi,

I am new to Qlik Sense and I have no previous coding background whatsoever.

I would like to create a bar stacked bar chart of Client A - 2017 that includes the sum of (Stream 1 , Stream 2 , Stream 3) in direct comparison to Client A - 2018 the sum of (Stream 1, Stream 2, Stream 3). I have a list of 30 clients so I would like to apply the Filter Pane to only show one client at a time, so the chart should only be side by side comparison of Client A for both years.

Currently the layout of the data that I inputted into Qlik looks like the attached and I would like it to look like the other attached.

How can I have two dimensions compared side by side, using the same field but filtered by year? And then using the filter pane to only see one client at a time?

Thanks!

Colin-Albert · ‎2018-11-21

You can do this using a bar chart with one dimension for Client and two expressions, one expression for 2017 sales, the other expression for 2018 sales.

The expression would use a set expression to select the year. something like

expression1 sum({<Year={2017} >} [Stream 1] + [Stream 2] + [Stream 3])

expression2 sum({<Year={2018} >} [Stream 1] + [Stream 2] + [Stream 3])

or you could use a formula so the year is not hardcoded.

expression1 sum({<Year={$(=max(Year)-1)} >} [Stream 1] + [Stream 2] + [Stream 3])

expression2 sum({<Year={$(=max(Year))} >} [Stream 1] + [Stream 2] + [Stream 3])

You would need to replace the "year" field and the other fields with your actual field names.

Beginner · ‎2018-11-22

Hi Colin,

Thanks for the help. Can I ask, for the dimension I will open the expression editor and in put the attached?

The Stream field names are in green as these are replicas of my actual field names.

It still says invalid dimension? Is there anything else I need to do?

Colin-Albert · ‎2018-11-22

QlikView is case sensitive, so in QlikView 'Year' and 'year' are two different fields.

Can you show a screen shot showing the error in context or post a sample of you application.

Beginner · ‎2018-11-22

I have uploaded the same exact data as indicated to make it more realistic.

Here are the errors I am getting.

Colin-Albert · ‎2018-11-22

OK, you are using QlikSense, not QlikView.

The error is the dimension not the expression. What is the dimension on your chart?

Beginner · ‎2018-11-22

Yes I am using Qlik Sense.

Here is what it says under invalid dimension.

And what the data I have loaded/using looks like.

Colin-Albert · ‎2018-11-22

You have put the expression into the dimension not the measure.

You should use [Test Sheet.Client] as the dimension

and sum({<Year={2017} >} [Stream 1] + [Stream 2] + [Stream 3]) for measure1

sum({<Year={2018} >} [Stream 1] + [Stream 2] + [Stream 3]) for measure2

Beginner · ‎2018-11-22

Great, that works!

Thank you.

I have another chart I want to do the exact same thing for, but now the Streams that are fed into the sum to be differentiated by colour.

Right now, it's a grouped chart to show 2017 and 2018 right next to each other for each client, but now to have both a stacked and group chart in one, by differentiating each Stream of the sum of 2017 and 2018.

Beginner · ‎2018-11-23

Hey,

Does anyone have a solution to the above?