count if TOTAL results are correct but total of chart is not

[curiousfellow]

I have table with employees, year and department

I want the number of records per employee when there are more then one records for that employee.

My expression :

if (count total < department,empoyeenumber> {< year = {2020}, employed = {1}>} employeenumber) > 1,

count (total < department,empoyeenumber> {< year = {2020}, employed = {1}>} employeenumber)

I get 34 lines with value of the expression 2 but the total shows 24837 being the number of records (used selection with listbox. I do not understand why the total counts all dimensions

[avinashelite · Accepted solution]

You need to change the TOTAL Mode , go to expression > Toal Mode >change it according to your requirement

[curiousfellow]

Thank you , that did the trick. Never paid attention to that. 

Perhaps you could help me some more, actually I only want to have the total (in this case 68) in a text box. What would be the expression then ?.  

[Kushal_Chawda · Accepted solution]

@curiousfellow  try wrapping your expression in aggr function to get the correct count in table. You can use the same expression in text object as well.

sum(aggr(

if (count total < department,empoyeenumber> {< year = {2020}, employed = {1}>} employeenumber) > 1,

count (total < department,empoyeenumber> {< year = {2020}, employed = {1}>} employeenumber),

department,empoyeenumber ))

[curiousfellow]

Thank you both for your quick replies