topic Two dimensions, side by side, filtered in QlikView

Two dimensions, side by side, filtered

Beginner — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Two dimensions, side by side, filtered

Colin-Albert — Wed, 21 Nov 2018 17:37:40 GMT

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.

Re: Two dimensions, side by side, filtered

Beginner — Thu, 22 Nov 2018 10:53:56 GMT

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?

Re: Two dimensions, side by side, filtered

Colin-Albert — Thu, 22 Nov 2018 11:34:24 GMT

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.

Re: Two dimensions, side by side, filtered

Beginner — Thu, 22 Nov 2018 11:45:41 GMT

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

Here are the errors I am getting.

Re: Two dimensions, side by side, filtered

Colin-Albert — Thu, 22 Nov 2018 11:50:49 GMT

OK, you are using QlikSense, not QlikView.

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

Re: Two dimensions, side by side, filtered

Beginner — Thu, 22 Nov 2018 11:57:52 GMT

Yes I am using Qlik Sense.

Here is what it says under invalid dimension.

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

Re: Two dimensions, side by side, filtered

Colin-Albert — Thu, 22 Nov 2018 12:40:15 GMT

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

Re: Two dimensions, side by side, filtered

Beginner — Thu, 22 Nov 2018 13:40:28 GMT

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.

Re: Two dimensions, side by side, filtered

Beginner — Fri, 23 Nov 2018 13:43:15 GMT

Hey,

Does anyone have a solution to the above?