Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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) & ')'
Thanks Gysbert. But the above gives me blank result for the count like - DeptA(), DeptB() 😞
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