Qlik Sense App Development

New Contributor III

Number of Rows that have a Value

How do I get the number of rows that have a value in a dimension?

I have tried:

Sum(Aggr(Count(DISTINCT {<ExpectedCases = {'*'}>} OperatorName),OperatorName))

noofrows(column(1))

Sum(if(isnum(\$(vEC))=-1,1,0))

max(rank(total column(1))

What I'm trying to do is modify this formula

(rank(total column(1))/(noofrows(TOTAL)/2))-1

so that denominator is number of records in the dimension that have a value instead of the total records in the dimension.

Please let me know if you have any questions.

1 Solution

Accepted Solutions
MVP

Re: Number of Rows the have a value

May be this

Count(TOTAL DISTINCT {<ExpectedCases = {'*'}>} OperatorName)

20 Replies
MVP

Re: Number of Rows the have a value

May be this

Count(TOTAL DISTINCT {<ExpectedCases = {'*'}>} OperatorName)

New Contributor III

Re: Number of Rows that have a Value

This works!  Thanks so much!

New Contributor III

Re: Number of Rows the have a value

Hi Sunny,

What if I want to know the total count of operators across a factory, line, shift?  I was thinking it would be

Aggr(Count(TOTAL DISTINCT {<ExpectedCases = {'*'}>} OperatorName), Factory, Line, Shift)

but that isn't working.

Thanks!

Ryan

MVP

Re: Number of Rows the have a value

Try this

Count(TOTAL <Factory> DISTINCT {<ExpectedCases = {'*'}>} OperatorName)

New Contributor III

Re: Number of Rows the have a value

Count(TOTAL <Factory> DISTINCT {<ExpectedCases = {'*'}>} OperatorName) gives the same result as Count(TOTAL DISTINCT {<ExpectedCases = {'*'}>} OperatorName).  Neither is the count of operators on all shifts.

MVP

Re: Number of Rows the have a value

What all are your chart dimensions here?

New Contributor III

Re: Number of Rows the have a value

factory, line, shift, employee

Also, thank you for answering my questions and being so helpful.  I am super appreciative.

MVP

Re: Number of Rows the have a value

Try this

Count(TOTAL <Factory, Line, Shift> DISTINCT {<ExpectedCases = {'*'}>} OperatorName)

New Contributor III

Re: Number of Rows the have a value

That returns a value of 1.  I am expecting 27.  I've attached my data.

The formula in the ranking column is rank(total column(1))

The formula in the total operators is column is Count(TOTAL <Factory, Line, Shift> DISTINCT {<ExpectedCases = {'*'}>} Employee)

What I'm ultimately trying to do is calculate each employee's percentile.  I do have employees in my data that do not have a score, which is why the requirement of having ExpectedCases data is in the formula.