Display Option

[qlikhereqlikthe]

Dear all,

I have a data over period of three years and I wish to display a combo chart to show the total sales and average sales per order. My dimensions are the clients (company A,B & C) and year (2016, 2017 & 2018). 

However, I face difficulty when I want to have Total Sales displayed as bar (expression: sum(total receivable), and average sales per order (expression: total receivable / count (distinct order no.) as symbol (dots), and then same time maintaining the flexibility where only data for selected years will display.

The issue i encountered is, with only two expression, the bars will only have one colour for all three years, instead of 3 colours for three different year, and colour for the average will be the same for three years.

Appreciate if anyone could enlighten me in this issue? Thank you!

[sasikanth]

HI,

Its always best o use only single dimensions in combo chart, 

instead use only Year as Dimension and below expressions

Exp1: sum({<company={'A'}>}total receivable )

Exp2: sum({<company={'B'}>}total receivable )

Exp3: sum({<company={'C'}>}total receivable )

Exp4:sum({<company={'A'}>}total receivable )/ (count (distinct{<company={'A'}>} order no) )

Exp5:sum({<company={'B'}>}total receivable )/ (count (distinct{<company={'B'}>} order no) )

Exp6:sum({<company={'C'}>}total receivable )/ (count (distinct{<company={'C'}>} order no) )

Thanks,

sasi

[uacg0009]

Hi,
I think you can try to use the "Background Color" in the Expression + button.
formula like:
if(year = 2016,green(),if(year = 2017,green(),blue()))
Aiolos

[qlikhereqlikthe]

Dear @uacg0009,

Thank you for your insight. I've tried, but the revised bar chart is unable to give me key information (eg. sum of sales) for different years. It will sum up the sum of sales for three years and show me in a colour instead. 

Dear @sasikanth,

Thank you for your insight. There is more than just 3 client under my portfolio, thus I'm afraid it is very time-consuming to have expression for each of the client.

Cheers!

[uacg0009]

Hi,
I see the image and I think you use the "year" to be the second dimension,right?
then if you use sum(sales) as expression, it will show 3 bars for every clients, right?
why do you say that it will sum up for 3 years?
if possible,could you please share a small sample, so I think try it?
Aiolos Zhao