topic Expression Color Change based on Set Analysis > in QlikView

Expression Color Change based on Set Analysis >

jcampbell — Wed, 16 Nov 2011 21:34:34 GMT

I need some help with an express I'm trying to use to change the color of a bar chart. I have a bar chart object with one dimension (Year) and three expressions (Expenses, Deposits and Difference). I'm using set analysis in these to calculate to the totals:

1. Expenses: SUM({$<[Category]={'Expense'}>}[Amount])

2. Deposits: SUM({$<[Category]={'Deposit'}>}[Amount])

3. Difference: SUM({$<[Category]={'Deposit'}>}[Amount]) - SUM({$<[Category]={'Expense'}>}[Amount])

Then for the third bar chart element (Difference) I'm trying to change the color based on whether or not the difference is a positive or negative one (i.e. green or red). Here is what I'm using:

IF(SUM({$<[Category]={'Expense'}>}[Amount]) > SUM({$<[Category]={'Deposit'}>}[Amount]), Red(), IF(SUM({$<[Category]={'Deposit'}>}[Amount]) > SUM({$<[Category]={'Expense'}>}[Amount]), Green(), Yellow()))

The result is that it is green (always). Any help would be greatly appreciated.

Thanks,

Josh

Expression Color Change based on Set Analysis >

stevedark — Wed, 16 Nov 2011 21:45:47 GMT

The expression looks okay, where do you have it applied, against the dimension or the expression? This will give you different results.

Can you post up an example with dummy data showing it not working?

The expression could be slightly simplified by doing:

if (SUM({$<[Category]={'Deposit'}>}[Amount]) - SUM({$<[Category]={'Expense'}>}[Amount]) > 0, Green(), Red())

Obviously that doesn't do your exactly equal - but if that was the situation then there would be no bar to colour.

What can make things simpler and more efficient at display time is to have at least two value fields, created in yoru load script, thusly:

LOAD

[...]

Category,

Amount,

if(Category = 'Deposit', Amount, 0) as [Deposit Amount],

if(Category = 'Expense', Amount * -1, 0) as [Expense Amount],

[...]

That way you can do away with the set analysis and just sum the two fields.

Another point, is that the default colours are not always great, you are better specifying rgb values, eg. rgb(200, 255, 200).

Hope that helps,

Steve