topic Re: Chart expressions/chart dimensions question in Water Cooler

Chart expressions/chart dimensions question

Thu, 24 May 2012 21:01:55 GMT

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

Chart expressions/chart dimensions question

Jason_Michaelides — Thu, 24 May 2012 22:59:30 GMT

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

Chart expressions/chart dimensions question

Fri, 25 May 2012 14:51:19 GMT

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

Re: Chart expressions/chart dimensions question

Jason_Michaelides — Fri, 25 May 2012 16:21:10 GMT

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

Jason

Chart expressions/chart dimensions question

Fri, 25 May 2012 18:55:23 GMT

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

Re: Chart expressions/chart dimensions question

Jason_Michaelides — Fri, 25 May 2012 19:32:03 GMT

But what will control the height of the bars?

Re: Chart expressions/chart dimensions question

Fri, 25 May 2012 19:42:21 GMT

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).

Re: Chart expressions/chart dimensions question

Jason_Michaelides — Fri, 25 May 2012 20:06:57 GMT

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

Re: Chart expressions/chart dimensions question

Fri, 25 May 2012 20:37:15 GMT

That worked!!! Thanks again!

Re: Chart expressions/chart dimensions question

Jason_Michaelides — Fri, 25 May 2012 21:15:02 GMT

Pleasure.