Lock stacked bar chart

[Santboig]

Hello all,

I have to implement a top 5 bar chart sales of customers with pending payments in Qlik Sense and this chart has to be fixed or locked.

The first step is creating the measure Sales and I coded its expression as:

Sum({1 <Payment = {'Pending'}>} Sale)

Then I limit Customer dimension to a fixed number of first 5 customers. I need to sort the customers by its sales in descendent order so I add the next expression to Order --> Customer:

Aggr({1} Sum({1 <Payment = {'Pending'}>} Sale), Customer )

If I try to filter with any dimension, this bar chart remains fixed. But now they asked me to add another dimension to know the sales whether the contracts of the payments have orders or not. So I had to create this new dimension, add it to the chart and convert it to an stacked bar chart. But now, if I filter, the order of the customer changes but mantaining the same top 5.

Any idea how I could lock this graph adding this second dimension?

Thanks.

[msKarthikeyan]

Hi Santboig,

You can just use Sum({1<Payment = {'Pending'}>}Sales)

No need for aggregation.

-Karthik 

[Santboig]

Hi Karthikeyan,

Thanks for the reply. Your code helped me to maintain the order in the stacked bar charts.

Now the probem is if I filter, the color expression is not respected and don't know why

=if(Order = 'Pending', '#990000', '#EC1111')

[msKarthikeyan]

Hi Santboig,

Its working for me. did u try without the aggregation..

 

can u give a screenshot for better understanding 

-Karthik 

[Santboig]

Hi,

Customer asked me to change the colors. Now the color expression is:

=if(Order='Pending', '#6F7779', '#EC0000')

So, this is an snapshot of the sheet. The issues are with the horizontal stacked bar charts. This is the original layout without filtering:

Without filtering

The chart on the left are the top 5 customer on sales by money amount. Then, the right one, are their respective number of contracts. The ones with orders are in red colour and the ones with no orders are in grey. The first issue is that the number of contracts of top 5 customer on sales don't match on the fifth customer. The expressions of the right chart are:

Number of contracts measure: count({1 < Agrupacion_Fase = {'Pdte de formalizar'}>} ANX), and dimensions are Cliente ('customer', for the stack) and Pedido ('order', for the bar).

Then, on the Sort tab, I have Cliente, Pedido and Number of contracts. The expression to sort Cliente is =sum({1 <Agrupacion_Fase = {'Pdte de formalizar'} >} Importe) (Importe are the sales), and it is the same expression used as measure in the chart on the left side.

On the other part, filtering anything on the data should'nt change those charts. But if I select anything on the bottom table, I get:

As you can see, the numbers and regions remain the same. But some regions have change their colour, even having the right numbers. I think that this also happens in the chart you posted (look at the first left bar). There is a little thin white line spliting the 'with orders/no orders' regions. And depending on the filter, all the 'no orders' regions that should remain grey become red.

[Santboig]

Hi again,

I solved the 'with orders/ no orders' color issue by creating a master dimension and changing its colors, as showed in the snapshot:

Now the last issue remaining is the contract of the top 5 customer by sales (the chart on the right) that the fifth customer does not match. It is curious that if I don't limit the number of the customers to a fixed number 5, they appear in the correct order. But limiting them, shows another thing...

[Santboig]

Hi again,

Finally, I found a solution to get the chart of the number of contract of the top 5 sellers.

In the Sort tab, Customer order by expression will remain the same (descending order, that didn't point it before):

=sum({1 <Agrupacion_Fase = {'Pdte de formalizar'} >} Importe)

And the number of contract measure is now:

Count({1 < Cliente = {"=Rank(Sum({1 <Agrupacion_Fase = {'Pdte de formalizar'}>} Importe),4)<=5"}, Agrupacion_Fase = {'Pdte de formalizar'} >} Contrato)

Yes, it's a little bit tricky 😛