9 Replies Latest reply: Jan 12, 2012 1:20 PM by jeffrush

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.

• Set Analysis, Dimensions and Bar Charts Oh My!

Try

Avg(Total Sales.SalesAmount)  to get all

• Set Analysis, Dimensions and Bar Charts Oh My!

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!

• Set Analysis, Dimensions and Bar Charts Oh My!

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?

• Set Analysis, Dimensions and Bar Charts Oh My!

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)

• Set Analysis, Dimensions and Bar Charts Oh My!

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.

• Set Analysis, Dimensions and Bar Charts Oh My!

Try

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

replace Sales.Salesperson with your appropriate field.

• Set Analysis, Dimensions and Bar Charts Oh My!

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

• Set Analysis, Dimensions and Bar Charts Oh My!

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?

• Set Analysis, Dimensions and Bar Charts Oh My!

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!