Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am loading from two different QVDs.
The first QVD has the calls data such as Number called, duration.. and the second QVD has the Employee information such as Names, Departments..
I have excluded some of the departments as I need to do.
While building a chart to show the total Nr called by Department I have on of the columns in the chart has no Department name.
i know the reason is that I have excluded some of the departments..
How can I hide this column from the chart?
Thanks for your help
There is a way to dynamically put all the departments in a set analysis expression, which I've outlined below. However, it does sound to me like you'd be best to join the department and calls tables together in the script and then use the "suppress null values" as previously suggested. Or flag null departments in the script and use a simple set analysis expression Count({<Flag={Yes}>} NrCalled)
It's almost always best to do the calculations in the script if they don't depend on user selections as it reduces the load on the server in use. Of course the balance is potentially extended load times so you do need to evaluate on a case by case basis.
Anyway, to dynamically put all departments in the set analysis expression you need to build the expression in a variable and then evaluate the variable in the expression. So create a variable vNrCalls:
='Count({<Department={' & Concat(Distinct Chr(39)&Department&Chr(39),',') & '}>} NrCalled)'
Then in the chart expression just put in $(vNrCalls) which will evaluate to
Count({<Department={'A','B','H','J'}>} NrCalled) with whatever departments are available in the current selections. If you always want all departments then obviously adjust the set analysis with the appropriate {1}
Hope this helps,
Jason
Oops! Just realised that either the webpage or my iPhone has replaced & with &amp...you'll need to adjust!
Oh I give up - you get the idea!!