Why does this return invalid data type? Any suggestions?
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.
Best Answer
-
Ok. Lets try this...
=LARGE(COLLECT([Issue Count]:[Issue Count], Created:Created, IFERROR(DATEONLY(@cell), TODAY(-31)) > TODAY(-30)), 5)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Paul,
I tried your formula, but it comes back as invalid data type again. Anything else to try or change?
-
I tried this, but it still comes back as invalid data type. I'm putting this into a summary field if that helps any.
-
What type of column are your dates in?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Created is an auto-number/system generated date column. Issue count is just a text/number
-
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.
-
Are there any text values or errors in the [Issue Count] column?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
The large(collect()) formula works on the issue count column when I take out the date values.
-
=LARGE(COLLECT([Issue Count]:[Issue Count], [Issue Count]:[Issue Count], @cell > 3), 3)
-
Ok. Lets try this...
=LARGE(COLLECT([Issue Count]:[Issue Count], Created:Created, IFERROR(DATEONLY(@cell), TODAY(-31)) > TODAY(-30)), 5)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
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. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!