Scatter Chart

[#1]

Dears,

Now I need to create a scatter chart.

At the X axes I'll have the revenue and at the Y axes I'll have the satisfaction, and the points will be our companies.

I have lots of information about our companies.

On the Satisfaction.xls sheet I have the customers feedback from our companies and to calculate the average of each company I'm just using the exmpression: =avg([SATISFACTION]). It's working good.

At the Revenue.xls I have the revenue of our companies, and to calculate the total revenue of our companies I'm just using the expression: = sum([REVENUE]). But it's not working, because the chart is showing the same value of revenue for all of our companies. and the values showed is the GLOBAL TOTAL, and not the revenue for that specific compani.

How can I fix this problem?

Thanks

[#2]

Hi

Maybe use aggr function

Aggr( Avg( SATISFACTION) , COMPANIES)

Aggr( Avg( REVENIUE) , COMPANIES)

Let me know if it works

If not can you post a screenshot of thr DataModel (CTRL+T)

[#3]

Greate idea,

It solved my problem...

But now I have another problem.

In order to adjust the size of my chart on the X axes, I would like to create an expression to let the chart show the max value of all companies. Like if the QlikView calculated the revenue of all companies and then show the value of the biggest one.

How can I do it?

In other words to be more clear

how can I create an expression to show only the total amout of revenue of the BEST companies in terms of revenue?

Thanks

[erichshiino]

If you are using this:

Aggr( Avg( REVENUE) , COMPANIES)

then you can use

max( Aggr( Avg( REVENUE) , COMPANIES) ) as your axis expression.

If you want the sum, the expression would be very similar:

max( Aggr( sum( REVENUE) , COMPANIES) )

[#5]

Greate!

Now, I have another problem.

Some of the companies do not have it's name on the revenue datasheet, but they have on the satisfaction datasheet. So, how can I create an expression to do not show the companies that are not listed on one of those 2 datasheets?

Actually, they are presented as ZERO in the X axes.
I don't want to see than!

How can I do it?

[#6]

You can create a flag in the script in the revenu file

Load

Company , revenu , 1 as Flag_Rev

from Revenu.xls

Then create a listbox with Flag_Rev, then select only the compagny with Flag_Rev=1

[johnw]

Perhaps a calculated dimension like this:

aggr(if(avg(SATISFACTION) and sum(REVENUE),COMPANIES),COMPANIES)

For higher performance, load a "Company with both Satisfaction and Revenue" field in your script and use that as the dimension.

[#8]

No, it didn't worked.

Actually, I have 1 dimension and 2 expressions

The dimension is the [Service Center Nickname] and the expressions are:

Claim total: =num(SUM({<UNIQUE_FLAG={1}, [Status Claim] -={Cancelled}>}([Claim Total])*pick(match([Currency],'EUR','USD','BRL'),EUR,USD,BRL)),'USD #,##0.00', '.',',')

NPS: =avg([NPS])

What I would like to do is:
When the [Service Center Nickname] do not have a value of [Claim Total], then do not show anything. Neither the Claim Total, nor the NPS.

[johnw]

kellyhbastos wrote:No, it didn't worked.

Worked for me. See attached. Did I misunderstand the problem?