Need to count cells >0 from multiple tables and display Total Count

Avatar

Avatar
Validate 1
Level 1
lisab73316618
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile

Avatar
Validate 1
Level 1
lisab73316618
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile
lisab73316618
Level 1

22-11-2017

I created a performance evaluation form that has multiple tables (6) (all have two columns with the last row/column of each table a total of possible points for that section).

At the end of the form I want to be able to count all the 2nd columns (not the totals)  that are >0 and display that number.

Any help would be greatly appreciated.

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Springboard
MVP
Magus069
MVP

Likes

143 likes

Total Posts

1,078 posts

Correct reply

155 solutions
Top badges earned
Springboard
Validate 1
Ignite 3
Ignite 1
Establish
View profile

Avatar
Springboard
MVP
Magus069
MVP

Likes

143 likes

Total Posts

1,078 posts

Correct reply

155 solutions
Top badges earned
Springboard
Validate 1
Ignite 3
Ignite 1
Establish
View profile
Magus069
MVP

28-11-2017

Simply do a verification that values are greater than 0 each..

Answers (8)

Answers (8)

Avatar

Avatar
Establish
MVP
_Bruce_Robertson
MVP

Likes

271 likes

Total Posts

1,782 posts

Correct reply

372 solutions
Top badges earned
Establish
Give Back 25
Give Back 10
Give Back 5
Give Back 3
View profile

Avatar
Establish
MVP
_Bruce_Robertson
MVP

Likes

271 likes

Total Posts

1,782 posts

Correct reply

372 solutions
Top badges earned
Establish
Give Back 25
Give Back 10
Give Back 5
Give Back 3
View profile
_Bruce_Robertson
MVP

26-11-2017

Hi,

You can get a count of number of cells with a value greater than zero with an expression like;

Table1.resolveNodes("Row1.[NumericField1 > 0]").length

This assumes the second column is called NumericField1, and the table is called Table1 and the row called Row1 ... so you will probably have to change those.

You could have six expressions like this one in the calculate event of the you total field and add them together.

Regards

Bruce

Avatar

Avatar
Validate 1
Level 1
lisab73316618
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile

Avatar
Validate 1
Level 1
lisab73316618
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile
lisab73316618
Level 1

28-11-2017

That worked!  I have been struggling with this form for days...  Thank you so much!

Avatar

Avatar
Validate 1
Level 1
lisab73316618
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile

Avatar
Validate 1
Level 1
lisab73316618
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile
lisab73316618
Level 1

28-11-2017

I tried the following but maybe I'm missing something. (both fields could be blank at some point until the user starts filling in the blanks.

I googled this to get rid of my zero division  and I do get the calculation but I want to get rid of the error message.

Do I need another if statement? and how would I do that?

if(HasValue(Row2.RawCount&Row2.RawCount>0)) then (Row2.RawCount/Row3.Avail) else null endif

Avatar

Avatar
Springboard
MVP
Magus069
MVP

Likes

143 likes

Total Posts

1,078 posts

Correct reply

155 solutions
Top badges earned
Springboard
Validate 1
Ignite 3
Ignite 1
Establish
View profile

Avatar
Springboard
MVP
Magus069
MVP

Likes

143 likes

Total Posts

1,078 posts

Correct reply

155 solutions
Top badges earned
Springboard
Validate 1
Ignite 3
Ignite 1
Establish
View profile
Magus069
MVP

28-11-2017

Hi there,

it seems like one of those value are equal to 0.

You cannot divide a value by 0, so unless you make an if statement to make sure none of these values are equal to 0 you might get that message often.

Hope this help!

Avatar

Avatar
Validate 1
Level 1
lisab73316618
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile

Avatar
Validate 1
Level 1
lisab73316618
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile
lisab73316618
Level 1

27-11-2017

I forgot to add that the Raw and Avail cells are calculated if that makes a difference.  Thanks.

Avatar

Avatar
Validate 1
Level 1
lisab73316618
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile

Avatar
Validate 1
Level 1
lisab73316618
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile
lisab73316618
Level 1

27-11-2017

Bruce,

I'm sure that would work but I couldn't get it to with my limited knowledge.  However after poking around, I did get an if statement to calculate each table for me and then I used sum to add each to get to my total count.  Probably the long way around but it works.  Here's my next question if you can help.

I want to take the sum (Raw) and divide by the  Count (Avail) to get a Score.  I thought that was pretty simple but I get the following error using this.   - but it still gives me the correct answer. I would like to get rid of this error box.  Any help would be great.

form1.Subform9.Table7.Row4.Score::calculate - (FormCalc, client)

Score = Row2.Raw / Row3.Avail

1359533_pastedImage_2.png

Avatar

Avatar
Validate 1
Level 1
lisab73316618
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile

Avatar
Validate 1
Level 1
lisab73316618
Level 1

Like

1 like

Total Posts

6 posts

Correct reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile
lisab73316618
Level 1

23-11-2017

I'm sorry.  I am quite the novice with LC.  Can you give me more info or an example of an if statement that counts?

Avatar

Avatar
Validate 1
Level 4
MinusZero
Level 4

Likes

30 likes

Total Posts

326 posts

Correct reply

54 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile

Avatar
Validate 1
Level 4
MinusZero
Level 4

Likes

30 likes

Total Posts

326 posts

Correct reply

54 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 25
Boost 10
View profile
MinusZero
Level 4

23-11-2017

You will need to use a global variable to count the items that meet your 'if' logic and then display it in a textfield.