AVG number of tickets per day

Bryan Harris

I am wandering the best way to create an average number of tickets entered per day, but only if at least one ticket was entered that day, for a rolling 12 months based upon a creation date.

I am so new to Smartsheet that this has me all twisted up like a pretzel.

Bryan Harris

I found my answer. There were a few instances where the time was identical. So to get around this I create another field in my main sheet that pulled just the date field out to get my distinct count based upon the calendar day.

=COUNT(DISTINCT(COLLECT({CreationDate}, {HelpdeskYear}, [2020]$1)))

Even though we did not get the perfect answer together your suggestion definitely led me down the right path to get my answer and dig in to find out the problem and some gotchas to look out for when collecting and reporting on data.

Thank you so much

Paul Newcome

Welcome to Smartsheet!

You are going to want to first count how many days there were in the past 12 months.

=TODAY() - DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()))

Then you will count how many tickets were submitted between today and one year ago.

=COUNTIFS([Creation Date]:[Creation Date], @cell >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY())))

Then divide the number of days by the number of tickets.

=(TODAY() - DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()))) / COUNTIFS([Creation Date]:[Creation Date], @cell >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY())))

Bryan Harris

Thank you for the feedback, but I am trying to get a unique count of days in the last year, based upon a day that had at least one ticket entered. For example:

1/1/2020

1/1/2020

1/1/2020

1/2/2020

1/5/2020

1/5/2020

1/5/2020

1/6/2020

Would equal 4 days in 2020 that had tickets entered, with 8 total tickets, meaning I am averaging 2 tickets per day. If I just take total calendar days that would be 8 tickets in 6 days so less than one per day. I am trying to exclude weekends, holidays, vacation days, and any other day I am out of the office.

Does this make sense?

Paul Newcome

Ah. Ok. My apologies.

=COUNT(DISTINCT([Creation Date]:[Creation Date]))

This will give you the total number of dates that had a ticket submitted. Use that in place of the first bit.

=COUNT(DISTINCT([Creation Date]:[Creation Date])) / COUNTIFS([Creation Date]:[Creation Date], @cell >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY())))

Bryan Harris

Yea I just discovered this DISTINCT formula and was getting ready to give it a whirl.

I will let you know how it goes. At first blush it looks like it worked, but I did not exclude it to the year I wanted so I am adding that piece now.

Paul Newcome

UNTESTED

See if you can work the COLLECT function in.

Paul Newcome

TESTED

=COUNT(DISTINCT(COLLECT([Creation Date]:[Creation Date], [Creation Date]:[Creation Date], @cell>= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY())))))

Bryan Harris

So the collection formula worked exactly as this one: =COUNT(DISTINCT({created}))

If I wanted to only count the current year based upon a column header where do I slide the "IF" statement in?

So I am doing the formula at the bottom of a 53 row sheet. On row one I have 2019, 2020, 2021, etc.

If I want to count the distinct dates for 2019 only but use a column reference does this look right?

I tried with the COLLECT and it does not appear to be giving me the right numbers. It is giving me a 41 with 45 total tickets for the year 2020, and obviously this is way too many for this year. I manually did a count and this should be 4. I have only added tickets on the 3rd, 4th, 6th and 7th of this month.

=COUNT(DISTINCT(COLLECT({created}, {HelpdeskYear}, [2019]$1))))

I am wondering if I should do a COUNTIFS, but I am not sure if I could get a DISTINCT to work as one of my IF's.

Running on fumes here, will take a look again tomorrow.

Thanks for your help,

Harry

Paul Newcome

My apologies. When you said "Rolling Year" I assumed you meant going back one year from whatever today is.

Assuming "2019" is in [2019]1, this should work.

=COUNT(DISTINCT(COLLECT({created}, {created}, YEAR(DATEONLY(@cell)) = [2019]$1))))

This should count how many unique dates are in your column that have a year of 2019.

Do you have every single date in your column but with zeros in the dates where there are no tickets? I had also assumed that since we were using a "Created Date" the only dates in that column would be those that have at least one ticket submitted.

Bryan Harris

You are right I did say rolling year. I was thinking of a KPI to keep track of my ongoing avg ticket count to see if it is going up or down, but unless I am storing that over time I am not sure I would see the trend. I am going to use the weekly ticket count as my trends.

You are also correct, I do not have every date, it is the created date. I did create two other columns in my main sheet, one is helpdeskyear, that pulls just the year out and helpdeskweek, that is what I am using to count my tickets by week.

I entered your formula in the cell, but it comes back #unparseable.

I my formula "=COUNT(DISTINCT(COLLECT({created}, {HelpdeskYear}, [2019]$1))))" , if I am doing this right, I am counting the distinct values in the 'created' reference, where the helpdeskyear is equal to the value of 2019. This seems to be counting close to right based upon the criterion range equal to the criteria, but it is not giving me the distinct count of the created date. And for some reason the number is off by 3. I did this "=COUNTIF({HelpdeskYear}, [2020]$1)" and it gives me the correct count so not sure why the collect statement would be missing some.

Bryan Harris

I found my answer. There were a few instances where the time was identical. So to get around this I create another field in my main sheet that pulled just the date field out to get my distinct count based upon the calendar day.

=COUNT(DISTINCT(COLLECT({CreationDate}, {HelpdeskYear}, [2020]$1)))

Even though we did not get the perfect answer together your suggestion definitely led me down the right path to get my answer and dig in to find out the problem and some gotchas to look out for when collecting and reporting on data.

Thank you so much

Paul Newcome

As for the #UNPARSEABLE issue, my formula had 1 too many closing parenthesis on the end. My apologies.

Using the YEAR(DATEONLY(@cell)) when looking at the created date essentially replicates the function of your HelpdeskYear column. It pulls the DATEONLY from the Created column then looks at the year.

In essence (had I not included one too many parenthesis, my formula is doing the same thing as your formula/column. Same idea. Different solution. Haha.

I am glad I was able to help you get on the right track. 👍️

Please don't forget to mark the most appropriate comment as the "Accepted Answer" so that others know a solution has been found.

Bryan Harris

This formulas only returned a '1'

=COUNT(DISTINCT(COLLECT({created}, {created}, YEAR(DATEONLY(@cell)) = [2019]$1)))

That made me think that the criterion was only looking at the year portion of the date and the distinct only picked the 2020 year.

I am not sure which answer to pick. Technically it was mine, but not without a ton of your help.

Paul Newcome

Hmm... I think you may be right. Good catch! 👍️ Although that seems (at least to me) a little odd that it worked that way.

If your comment was the comment that contained the best solution, then mark your comment. I am honestly not worried about who "gets the credit" even if your correct solution was derived through my help. What matters is that a solution was found. Marking a response as the best answer accomplishes a few things.

1. It lets other people looking to help know that you have a solution.
2. It lets other people looking for a similar solution know that one can be found.
3. It moves that particular comment to the top of the list of comments so that it is easier to find.

At the end of the day, your response was the one that provided a working solution. Kudos to you for figuring it out. 👍️🤙

EDIT: If you don't do it, I'll just flag it and bring it to the Mods's attention so they can mark it. 😋 Hahaha.