Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis, Dimensions and Bar Charts Oh My!

Hello all.

I am quite new to QlikView so any input would be most helpful.

I have a Bar chart with some simple data points.

It is using salesperson as the dimension.

While I can get the average sale amount per sales person for the selected date period, I want to find the average sale amount across -all- sales people for that selected date period so I can determine individual variance.

Avg({$} Sales.SalesAmount)  of course gets me the average individual for the selected period.

Avg({1} Sales.SalesAmount) would get me the average for the individual for all available data.

I'm afraid I'm just not mentally making the leap to what the solution should be but I am hoping it is fairly straight-forward and one of you will be able to help me.

Thank you in advance for your time.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Yes, your right.

Try

Avg({$} Sales.SalesAmount) - Avg({<Sales.Salesperson= >} Total Sales.SalesAmount)

replace Sales.Salesperson with your appropriate field.

View solution in original post

9 Replies
Not applicable
Author

Try

Avg(Total Sales.SalesAmount)  to get all

Not applicable
Author

Well, I was looking for simple and quick. You definitely delivered

Although I wish it hadn't been so simple.

Ah well, appreciate the help!

Not applicable
Author

One last thing:

Avg({$} Sales.SalesAmount) - Avg(Total Sales.SalesAmount)

This does provide me with the individual sales person variance from average however now when I select a bar for an individual sales person to drill into, it now shows me 'No data to display'

Instead of showing me just that one person.

Any ideas?

swuehl
MVP
MVP

I believe if you select one salesperson (given than salesperson is your dimension), your above expression returns zero. If suppress zero values is enabled in presentation tab, you probably get 'No data to display' returned.

Try

Avg({$} Sales.SalesAmount) - Avg({1} Total Sales.SalesAmount)

Not applicable
Author

Hey Swuehl.

I'm afraid that expression does not give the correct results.

I believe that it isn't taking into account the selected time period.

swuehl
MVP
MVP

Yes, your right.

Try

Avg({$} Sales.SalesAmount) - Avg({<Sales.Salesperson= >} Total Sales.SalesAmount)

replace Sales.Salesperson with your appropriate field.

Not applicable
Author

That is back to providing the correct results, but still has no data to display on drilldown.

swuehl
MVP
MVP

Well, can't reprouce that here. It seems to me that you are using a group as dimension, right?

Could you upload a small sample, or describe your setting a bit closer?

Not applicable
Author

Problem solved, I accidentally user the wrong field for the Sales.SalesPerson= section.

Made that change and voila! everything worked.

Thank you so much for your help!