topic Re: Number of Rows that have a Value in App Development

Number of Rows that have a Value

Anonymous — Mon, 04 Dec 2017 23:14:38 GMT

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.

Re: Number of Rows the have a value

sunny_talwar — Mon, 04 Dec 2017 23:57:20 GMT

May be this

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

Re: Number of Rows that have a Value

Anonymous — Tue, 05 Dec 2017 16:13:59 GMT

This works! Thanks so much!

Re: Number of Rows the have a value

Anonymous — Fri, 22 Dec 2017 19:42:29 GMT

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

Re: Number of Rows the have a value

sunny_talwar — Fri, 22 Dec 2017 21:06:18 GMT

Try this

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

Re: Number of Rows the have a value

Anonymous — Wed, 27 Dec 2017 18:50:46 GMT

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.

Re: Number of Rows the have a value

sunny_talwar — Wed, 27 Dec 2017 19:46:10 GMT

What all are your chart dimensions here?

Re: Number of Rows the have a value

Anonymous — Wed, 27 Dec 2017 19:58:06 GMT

factory, line, shift, employee

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

Re: Number of Rows the have a value

sunny_talwar — Wed, 27 Dec 2017 20:04:53 GMT

Try this

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

Re: Number of Rows the have a value

Anonymous — Wed, 27 Dec 2017 20:45:42 GMT

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.

Re: Number of Rows that have a Value

sunny_talwar — Wed, 27 Dec 2017 20:49:30 GMT

But this should give you 27, right?

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

If not, then try this

Aggr(NODISTINCT Count(DISTINCT {<ExpectedCases = {'*'}>} Employee), Factory)

Re: Number of Rows that have a Value

Anonymous — Wed, 27 Dec 2017 21:09:32 GMT

Count(TOTAL DISTINCT {<ExpectedCases = {'*'}>} Employee) gives me 7, which is the distinct number of employees on the unique factory/line/shifts in the data.

Aggr(NODISTINCT Count(DISTINCT {<ExpectedCases = {'*'}>} Employee), Factory) also gives me 7.

Re: Number of Rows that have a Value

sunny_talwar — Wed, 27 Dec 2017 21:17:25 GMT

‌Remove DISTINCT from within Count

Re: Number of Rows that have a Value

Anonymous — Wed, 27 Dec 2017 21:45:52 GMT

That also returns 7. Which makes sense since there's 7 operators in the data. Should there be a count of operators aggregated over factory, line, shift?

Re: Number of Rows that have a Value

sunny_talwar — Wed, 27 Dec 2017 21:46:54 GMT

This returned 7 also?

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

Re: Number of Rows that have a Value

Anonymous — Wed, 27 Dec 2017 21:50:40 GMT

Yes it did.

Re: Number of Rows that have a Value

sunny_talwar — Wed, 27 Dec 2017 21:54:50 GMT

How about this

Count(TOTAL {<ExpectedCases = {'*'}>} Employee&Shift&Line&Factory)

Re: Number of Rows that have a Value

Anonymous — Wed, 27 Dec 2017 22:25:51 GMT

That returns 5239. I don't know what that result means.

Re: Number of Rows that have a Value

sunny_talwar — Wed, 27 Dec 2017 22:28:55 GMT

How about if you now add DISTINCT

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

Re: Number of Rows that have a Value

Anonymous — Wed, 27 Dec 2017 22:40:53 GMT

That worked! I can't believe it! Thank you! Thank you! Thank you!

This also works, Count(TOTAL DISTINCT {<InspectedCount = {'*'}>} PlantName&LineName&ShiftNumber).

So this reason this works is I'm counting the total distinct operators that have data across each unique dimension.