Count including current selection

[#1]

Hi, I am creating a trellis chart and looking to create a dynamic title for each sub-chart such that each title should show me the total count beside each department name. However, as my department comes from a calculated dimension, I am unable to concatenate the count based on current selection.

Eg. Dimension: if(lbtype='Preferred' and ImpDept=ActDept, ActDept) & '(' & Count({$<Year={$(vCurrentYear)}>} Distinct ID) & ')'

This gives me the Department Name concatenated with the count for this year across all departments. However, I need to count based on year + the currently selected department.

Please advise.

[Gysbert_Wassenaar]

If you say 'currently selected department', does that mean that you actually selected a department first?

Perhaps you can show us what you're doing by posting a small qlikview document that demonstrates the problem.

[#3]

I am unable to post the exact data. But to reiterate, if I create a trellis chart (enabled on Department Name) the individual chart title will show the concerned dept name. What I want is also the total count of applications under it as such - DeptA(20), DeptB(40) and so on.

However, I am not using all the departments in my data and filtering the departments in the dimension as below- if(lbtype='Preferred' and ImpDept=ActDept, ActDept).

Now when I try to concatenate the application count to the department name, it counts based on the current year but doesn't aggregate based on dept. Thus I get same count for each dept name.

The dimension I'm using is as below:

if(lbtype='Preferred' and ImpDept=ActDept, ActDept) & '(' & Count({$<Year={$(vCurrentYear)}>} Distinct ID) & ')'

How can I modify this to aggregate the count based on each department?

Hope the above helps.

[Gysbert_Wassenaar]

Ok, you need a calculated dimension. Something like:

if(lbtype='Preferred' and ImpDept=ActDept, ActDept) & '(' & Aggr(if(lbtype='Preferred' and ImpDept=ActDept,Count({$<Year={$(vCurrentYear)}>} Distinct ID)),ActDept) & ')'

[#5]

Thanks Gysbert. But the above gives me blank result for the count like - DeptA(), DeptB() 😞

[Gysbert_Wassenaar]

Please post a small qlikview document. Exact data is not needed. Only enough data to reproduce the problem. See this document: Preparing examples for Upload - Reduction and Data Scrambling