Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Yes, your right.
Try
Avg({$} Sales.SalesAmount) - Avg({<Sales.Salesperson= >} Total Sales.SalesAmount)
replace Sales.Salesperson with your appropriate field.
Try
Avg(Total Sales.SalesAmount) to get all
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!
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?
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)
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.
Yes, your right.
Try
Avg({$} Sales.SalesAmount) - Avg({<Sales.Salesperson= >} Total Sales.SalesAmount)
replace Sales.Salesperson with your appropriate field.
That is back to providing the correct results, but still has no data to display on drilldown.
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?
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!