topic Re: Count including current selection in QlikView

Count including current selection

Mon, 12 Oct 2015 13:53:52 GMT

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.

Re: Count including current selection

Gysbert_Wassenaar — Mon, 12 Oct 2015 15:45:49 GMT

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.

Re: Count including current selection

Mon, 12 Oct 2015 15:59:28 GMT

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.

Re: Count including current selection

Gysbert_Wassenaar — Mon, 12 Oct 2015 16:39:33 GMT

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) & ')'

Re: Count including current selection

Mon, 12 Oct 2015 16:50:44 GMT

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

Re: Count including current selection

Gysbert_Wassenaar — Tue, 13 Oct 2015 05:06:57 GMT

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