Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
rmmetcalf
Contributor III
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.

20 Replies
sunny_talwar

But this should give you 27, right?

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

If not, then try this

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

rmmetcalf
Contributor III
Contributor III
Author

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.

sunny_talwar

‌Remove DISTINCT from within Count

rmmetcalf
Contributor III
Contributor III
Author

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?

sunny_talwar

This returned 7 also?

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

rmmetcalf
Contributor III
Contributor III
Author

Yes it did.

sunny_talwar

How about this

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

rmmetcalf
Contributor III
Contributor III
Author

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

sunny_talwar

How about if you now add DISTINCT

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

rmmetcalf
Contributor III
Contributor III
Author

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.