topic Scatter Chart in QlikView

Scatter Chart

Tue, 26 Apr 2011 19:49:32 GMT

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

Scatter Chart

Tue, 26 Apr 2011 20:25:22 GMT

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)

Scatter Chart

Tue, 26 Apr 2011 20:36:12 GMT

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

Scatter Chart

erichshiino — Tue, 26 Apr 2011 20:43:26 GMT

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

Scatter Chart

Tue, 26 Apr 2011 20:53:37 GMT

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?

Scatter Chart

Tue, 26 Apr 2011 20:57:24 GMT

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

Scatter Chart

johnw — Wed, 27 Apr 2011 02:04:10 GMT

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.

Scatter Chart

Wed, 27 Apr 2011 13:16:56 GMT

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.

Scatter Chart

johnw — Wed, 27 Apr 2011 19:21:20 GMT


kellyhbastos wrote:No, it didn't worked.

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