Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading from 2 QVDs then exclud some data

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

12 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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={' &amp; Concat(Distinct Chr(39)&amp;Department&amp;Chr(39),',') &amp; '}>} 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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Oops! Just realised that either the webpage or my iPhone has replaced &amp; with &amp;amp...you'll need to adjust!

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Oh I give up - you get the idea!!