Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
But this should give you 27, right?
Count(TOTAL DISTINCT {<ExpectedCases = {'*'}>} Employee)
If not, then try this
Aggr(NODISTINCT Count(DISTINCT {<ExpectedCases = {'*'}>} Employee), Factory)
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.
Remove DISTINCT from within Count
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?
This returned 7 also?
Count(TOTAL {<ExpectedCases = {'*'}>} Employee)
Yes it did.
How about this
Count(TOTAL {<ExpectedCases = {'*'}>} Employee&Shift&Line&Factory)
That returns 5239. I don't know what that result means.
How about if you now add DISTINCT
Count(DISTINCT TOTAL {<ExpectedCases = {'*'}>} Employee&Shift&Line&Factory)
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.