Chart expressions/chart dimensions question

[#1]

I am new to QV and have a question regarding the chart expressions/dimensions.  I current have data pertaining to healthcare.  I have a healthcare "collector name" field and an "account balance" field.  I would like the chart to display the "collector name" and the associated aggregate "account balance" for that "collector name." However, I would also like a thrid field that groups the aggregated "account balances" into three seperate categories. I would like a "0-1000", "1001-2000" and ">2000" account balance grouping per "collector name". I am able to accomplish this task in the Script Loader section, but I would like to know how to do it using the chart expression and chart dimension side. Please see the example below. Thoughts?

Collector Name Account Balance (at the aggregate level)  Balance Catoegory

Nate B.                     $3000.00                                                   >2000

Drew M.                   $1001.00                                                   1001-2000

[Jason_Michaelides · Accepted solution]

SO a straight table chart with Collector Name as the dimension, Sum(Account Balance) as the first expression and for the second expression, try:

IF(Sum(Account Balance)<=1000,'0-1000',IF(Sum(Account Balance)<=2000,'1001-2000','>2000'))

Hope this helps,

Jason

[#3]

Jason,

I tried your recommendation, but it did not work.  I believe that since it I was using an expression, it will not allow you to use ' ' (single quotes) with non-numeric values. If I use IF(Account Balance<=1000,'0-1000',IF(Account Balance<=2000,'1001-2000','>2000')) in the Dimension section of the chart, that will work, but it does not aggregate the account balance at the collector name.  I tried IF(Sum(Account Balance)<=1000,'0-1000',IF(Sum(Account Balance)<=2000,'1001-2000','>2000')) in the Dimension section, but that does not work either. Any other thoughts?

Iain

[Jason_Michaelides]

Sorry mate - not sure what you are doing wrong! Works fine for me...see attached.

Jason

[#5]

Jason,

I was able to get your solution to work in a straight table. What if I wanted to able the same logic to a Bar Graph? That is where I have been struggling. Thanks again.

Iain

[Jason_Michaelides]

But what will control the height of the bars?

[#7]

So the bar graphs would show the total account balance per collector name. Each bar for each collector name would also be colored coded pending on what category they fell into (i.e. 0-1000, 1001-2000, >2000).

[Jason_Michaelides]

Ok no problem. Dimension should be collector name. Expression should be Sum(AccountBalance). Then, in the expressions tab, expand the expression and click background colour. In the new expression box put:

IF(Sum(Account Balance)<=1000,RGB(xxx,xxx,xxx),IF(Sum(Account Balance)<=2000,RGB(yyy,yyy,yyy),RGB(zzz,zzz,zzz))

Hope this helps,

Jason

[#9]

That worked!!! Thanks again!

[Jason_Michaelides]

Pleasure.