Help With Colourmix1 Expression

[#1]

Hello everyone,

I have a colourmix expression that looks like so:

Colormix1(sum($(vExpenditureAmount))-$(=min(aggr(sum($(vExpenditureAmount)), [Expense Category]))))/$(=(max(aggr(sum($(vExpenditureAmount)), [Expense Category]))-min(aggr(sum($(vExpenditureAmount)), [Expense Category])))),white(), $(vDenim))

The variable, vExpenditureAmount, looks like so:

sum([Session Transaction Fee]) +

sum([Merchandise Shipping Fee Charged]) +

sum([Institutional Transaction Fee]) +

sum([Institutional Transaction Fee]) +

sum([University Transaction Fee]) +

sum([Marketing Cost]) +

sum([Admin Expense]) +

sum([Operation Cost]) +

sum([Misc. Cost]) +

sum([IT Expenses Cost]) +

sum([IT Investment Total]) +

sum([Salary Total]) +

sum([CP Total])

When writing the colourmix1 expression I am given no error message and when I press apply, my piechart remains grey.

What is it that I am doing wrong?

Thank you in advance,

Alison

[Lesny_Eva]

I assume that the formula you use gives a "wrong" result. Colormix expects a value between 0 and 1.

Colormix1 (Value , ColorZero , ColorOne)

"Value" needs to be a real number between 0 and 1.

How does your vDenim look like? It could also be that that one is not correct and therefore Qlik doesn't know what colour to use.

[#3]

Hello Eva,

Thank you for your response.

Sorry, I am unsure of what you mean by my formula giving a 'wrong' result.

vDemin looks like this:

RGB(21, 96, 189)

I'm pretty sure there's no issue with that as I've used it in other visualisations without complaint.

[Lesny_Eva]

The calculation you are using:

sum($(vExpenditureAmount))-$(=min(aggr(sum($(vExpenditureAmount)), [Expense Category]))))/$(=(max(aggr(sum($(vExpenditureAmount)), [Expense Category]))-min(aggr(sum($(vExpenditureAmount)), [Expense Category]))))

needs to between 0 and 1. If it is 2 it cannot be coloured.

I made a dummy table:

I used this to colour the column

ColorMix1(if(wildmatch(City, 'Bern'),2, if(wildmatch(City, 'Berlin'),0,1)), blue(), red())

The number is calculated using the same formula.

If the city is Bern, assign it the value 2, if it's Berlin the value 0 and everything else gets the value 1

As you can see, it correctly colours "1" with red, and "0" with blue. The "2" is not coloured though, and I didn't get an error in my formula (as Qlik doesn't know it can contain numbers bigger than 1)

[#5]

Hello Eva,

I think I understand somewhat. But I am unsure how how best to amend my expression in light of your comment. Do you know how I could do it?

[Lesny_Eva]

First way is to check whether I am right - best way might be a table where you can check what value it gives you for your selected row dimension.

Your expression is really complicated, so I have no idea what it actually does.

But checking what kind of values you get might push you in the right direction on what you could change in the expression.