Why does this return invalid data type? Any suggestions?

Adam Qualls

I'm trying to get a rolling 30 day issue count to return the 5th top number so I can give a list of clients that have the top 5 highest volumes of issues in the last 30 days.

=IF([Rolling 30]:[Rolling 30], 1, LARGE([Issue Count]:[Issue Count], 5))

My Rolling 30 column is a checkbox with a formula in it. =IF(TODAY(-30) < Created@row, 1)

My Issue Count column is a calculated number. =IF(Checkbox@row = 1, COUNTIF([Client Code]:[Client Code], =[Client Code]@row))

My thought was to make a rolling 30 checkbox column. This would be checked if the created date on the row is greater than today(-30). This seems to be functioning.

I would then only look at the rows that have a check mark in the Rolling 30 column and from there I would use the large function to pull the 5th largest number from the set.

After that I would pull it into a report and then create a chart with the results.

Paul Newcome

Ok. Lets try this...

=LARGE(COLLECT([Issue Count]:[Issue Count], Created:Created, IFERROR(DATEONLY(@cell), TODAY(-31)) > TODAY(-30)), 5)

Paul Newcome

Try a LARGE/COLLECT combo.

=LARGE(COLLECT([Issue Count]:[Issue Count], Created:Created, DATEONLY(@cell)> TODAY(-30)), 5)

This will also remove the need for the checkbox column since the date is calculated directly in the formula.

Adam Qualls

Paul,

I tried your formula, but it comes back as invalid data type again. Anything else to try or change?

Adam Qualls

https://community.smartsheet.com/discussion/comment/260321#Comment_260321

I tried this, but it still comes back as invalid data type. I'm putting this into a summary field if that helps any.

Paul Newcome

What type of column are your dates in?

Adam Qualls

https://community.smartsheet.com/discussion/comment/260360#Comment_260360

Created is an auto-number/system generated date column. Issue count is just a text/number

Adam Qualls

I tried creating a new date column and moved the data into the date column, but it still comes back invalid data type when i use your formula.

Paul Newcome

Are there any text values or errors in the [Issue Count] column?

Adam Qualls

https://community.smartsheet.com/discussion/comment/260365#Comment_260365

There is a formula in that cell but no errors.

=IF(Checkbox@row = 1, COUNTIF([Client Code]:[Client Code], =[Client Code]@row))

I tried updating to

=IF(Checkbox@row = 1, COUNTIF([Client Code]:[Client Code], =[Client Code]@row), 0)

to get rid of any blanks, but that did not work either.

Adam Qualls

The large(collect()) formula works on the issue count column when I take out the date values.

Adam Qualls

=LARGE(COLLECT([Issue Count]:[Issue Count], [Issue Count]:[Issue Count], @cell > 3), 3)

Paul Newcome

Ok. Lets try this...

=LARGE(COLLECT([Issue Count]:[Issue Count], Created:Created, IFERROR(DATEONLY(@cell), TODAY(-31)) > TODAY(-30)), 5)

Adam Qualls

https://community.smartsheet.com/discussion/comment/260480#Comment_260480

Thanks! That seems to work for now. I will monitor to see if it is doing what it should be doing over a longer period of time.

Paul Newcome

It looks like there were some non-date values in the Created column. That is usually my first thought, but I figured it wouldn't be the issue considering it is a system generated column. That's odd, but at least it is working now. Happy to help. 👍️