Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Scatter Chart

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

8 Replies
Highlighted
Not applicable

Scatter Chart

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)

Highlighted
Not applicable

Scatter Chart

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

Highlighted
Partner
Partner

Scatter Chart

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

Highlighted
Not applicable

Scatter Chart

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?

Highlighted
Not applicable

Scatter Chart

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

Highlighted
MVP
MVP

Scatter Chart

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.

Highlighted
Not applicable

Scatter Chart

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.

Highlighted
MVP
MVP

Scatter Chart


kellyhbastos wrote:No, it didn't worked.


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