Solved: Bar Chart Trellis for more than 2 Dimensions. - Qlik Community

vidyasagar159 · ‎2018-02-02

Hello Everyone,

I am working on QlikSense. I know that we cannot use more than 2 dimensions in a Stacked Bar chart. So I have decided to create a multiple Barcharts with the 3rd dimension. Can someone please help with the logic. Please find the information below.

Dimension 1: YEAR (This goes on x-axis)

Dimension2: Fruits exp: Apple, Banana, Grapes (This goes to the colors)

Dimension3: if(aggr(sum(sales),company,year)>50,greater than 50',if(aggr(sum(sales),company,year)<50,'Less than 50',if(aggr(sum(sales),company,year)>50 and aggr(sum(sales),company,year)<100,'50 to 100',if(aggr(sum(sales),company,year)>100 and aggr(sum(sales),company,year)<200,'100 to 200')))). So i have 4 dimensions or Categories from this expression.

Measure: Sum(Sales)

Since I do not have an option to show 3 dimensions in one single bar chart. I want to split the bar chart into 4 based on my 3rd dimension expression.

Thanks in Advance,

-Vidya

vidyasagar159 · ‎2018-02-08

So this is how I fixed the issue. Thanks to the community and collaboration.

I created the Dimension3 in a Load script editor as

[Size Table]:

load

year,

company,

if(aggr(sum(sales),company,year)>50,greater than 50',if(aggr(sum(sales),company,year)<50,'Less than 50',if(aggr(sum(sales),company,year)>50 and aggr(sum(sales),company,year)<100,'50 to 100',if(aggr(sum(sales),company,year)>100 and aggr(sum(sales),company,year)<200,'100 to 200')))) as Size

resdient [Main Table]

group by year, Company;

Dimension2 i have created it in a dimension expression

if(wildmatch(Category1,'*Apple*') and Flag=0 then 'Green Apple',

if(Category2 = 'Grapes' and Flag =1 then 'Red Grapes',

'All other fruits'

)) as Fruits

Measure Expression: Now I want to split the bar charts based on the Size dimension.

Chart1: Sum({<SIZE={"greater than 50"}>}sales)

Chart2: Sum({<SIZE={"less than 50"}>}sales)

Chart3: Sum({<SIZE={"50 to 100"}>}sales)

Chart4: Sum({<SIZE={"100 to 200"}>}sales)

Now the final step is to convert all the Bar Charts into 100% stacked bars. To do so,

Sum({<SIZE={"greater than 50"}>}sales)/ AGGR(sum({<SIZE={"greater than 50"}>} total <year> sales)

,YEAR,category1,category2,Flag)

Final Result:

Thanks,

-Vidya

View solution in original post

sunny_talwar · ‎2018-02-02

May be create 4 different charts?

vidyasagar159 · ‎2018-02-02

Yes. I want to create 4 charts. But I am not getting the right logic actually I am new to this tool. I am trying to put the filter in measure expression but it's not working as expected.

Example:

Measure Expression for Chart 1:

if(aggr(sum(sales),company,year)>50,sum(sales)/aggr(nodistinct sum(sales),year)

But i am not getting the right results

Thanks,

-Vidya

sunny_talwar · ‎2018-02-02

You expression for the initial chart was just Sum(Sales)... why do you have aggr(nodistinct sum(sales),year) at the end of the second chart?

vidyasagar159 · ‎2018-02-02

Becuase i want 100% Stacked Bar chat.

Thanks,

-Vidya

sunny_talwar · ‎2018-02-02

So may be like this

Sum(Aggr(If(Sum(sales) > 50, Sum(Sales)), company, year))/Aggr(NODISTINCT Sum(sales), year)

vidyasagar159 · ‎2018-02-02

Sum(Aggr(If(Sum(sales) > 50, Sum(Sales)), company, year)) This expression is giving me the total sales of all the Fruits which is correct. But when i am hovering the cursor on each fruit the sales amount is showing wrong. Maybe i need to investigate more by converting bar chart to a pivot table. And i believe that is the reason i am not getting 100% stacked bar with the below expression.

Sum(Aggr(If(Sum(sales) > 50, Sum(Sales)), company, year))/Aggr(NODISTINCT Sum(sales), year)

Thanks,

-Vidya

sunny_talwar · ‎2018-02-02

May be you need to add Fruit to the Aggr()

Sum(Aggr(If(Sum(sales) > 50, Sum(Sales)), company, year, Fruit))

vidyasagar159 · ‎2018-02-02

Wish it can be that easy. Unfortunately, Fruit is also a calculated dimension.

Example:

Fruit Dimension Expression:

if(wildmatch(Category1,'*Apple*') and Flag=0 then 'Green Apple',

if(Category2 = 'Grapes' and Flag =1 then 'Red Grapes',

'All other fruits'

))

Here i am only interested in (Green Apples and Red Apples)

Thanks,

-Vidya

vidyasagar159 · ‎2018-02-02

I am just thinking of creating a Fruit dimension from Load Script Editor by creating a new LOAD table.

Thanks,

Vidya