Is it possible to do a "Count IF" in a Dynamic Text data set?

Jason_Picker.1
11 days ago
3replies
Answered

I have a grid that contains a number of rows and what I want to do is count the ones that are above or below a certain number. For example, let's say that I have a growth percentage and I want to know the number of rows that have a negative growth rate (i.e. Growth % < 0) or the number that is greater than 10 percent (0.10). I realize that I could create a separate report that filters the grid to only show those below zero but I am trying to minimize the number of reports that I create plus understand how to push the boundaries of the logic in dynamic text. I can use the Loop function to cycle through the rows and check each value and return a 1 for true and a 0 for false, but I'm not sure how to aggregate them. I see that the CountAvg and CountPercentile functions work exactly like I am looking for but I want to be able specify the number to base the count on.

Perhaps I may need to submit a feature request but I wanted to see if anyone knew a way to do it. For now I will create separate reports.

Thanks!

- imbarmarinescubar_pyram
- 10 days ago
- Reported - view
Hi ,

1. You can use the count if function with tabulate. Take the grid and drop it into tabulate, then create in the tabulate a cell that has the count if function. Then create a visual area of the cell and drop it into your presentation / publication or into dynamic text editor.

2. You can use this expression to mimic the count if directly from a dynamic text wrapping a discovery:

length(loop(if(data(index,0)>12000000," ",""), 0, NumberOfRows()))
- Jason_Picker.1
  
  10 days ago
  
  Reported - view
  Option #2 is an excellent method. Counting spaces is sweet! It worked like a charm for my user. Thanks!
- VP Product Management
- Ian_Macdonald
- 10 days ago
- Reported - view
Bah!

beat me to it. Had exactly the same Loop() code as him!

Ian