topic Set Analysis, Dimensions and Bar Charts Oh My! in QlikView

Set Analysis, Dimensions and Bar Charts Oh My!

Thu, 12 Jan 2012 17:14:33 GMT

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.

Set Analysis, Dimensions and Bar Charts Oh My!

Thu, 12 Jan 2012 17:16:08 GMT

Try

Avg(Total Sales.SalesAmount) to get all

Set Analysis, Dimensions and Bar Charts Oh My!

Thu, 12 Jan 2012 17:22:26 GMT

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!

Thu, 12 Jan 2012 17:33:21 GMT

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!

swuehl — Thu, 12 Jan 2012 17:47:04 GMT

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!

Thu, 12 Jan 2012 17:52:10 GMT

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!

swuehl — Thu, 12 Jan 2012 17:54:34 GMT

Yes, your right.

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!

Thu, 12 Jan 2012 17:57:29 GMT

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!

swuehl — Thu, 12 Jan 2012 18:10:51 GMT

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!

Thu, 12 Jan 2012 18:20:09 GMT

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!