Number of items in SharePoint list by date

[Jackson_Dhorty]

Hi,

I am trying to count number of items by date

Item        Date
2            6/15/2021
5            6/15/2021
1            6/15/2021
6            6/16/2021
4            6/17/2021

Logic is:

on 15th  = total count is 8

on 16th  = total count is 6

on 17th  = total count is 4

Can anyone help me on this?

Thanks

[Mira_Ghaly]

@Jackson_Dhorty 

I would suggest that you run a flow daily on the List you want to get the counts from , and Use Get Items with Filter

Expression:

Make sure you add the UTCNow Expression through the expression panel and mind the single quotes around it.

Created ge 'utcNow('yyyy-MM-ddT00:00:00Z')'

Then add the date and the Length of the Get Items into the new list.

You can then add a compose data operation to get the length of the items:

Compose Expression:

length(outputs('Get_items')?['body/value'])

Use then the output from the compose step to create a new item.

Hope this helps!

[Jackson_Dhorty]

Issue is I have to run flow on weekly basis and want to filter out every day total item number by date for example there are total 8 items in 15th june. Problem is, there are same date against multiple items numbers

 is there any way I can count items against same dates?

Thanks

[Mira_Ghaly]

@Jackson_Dhorty

So do you need to group by date and by item number? Is that's what you're trying to achieve?

[Jackson_Dhorty]

I am trying to count total number of items against date 

item       date

2            6/15/2021
5            6/15/2021
1            6/15/2021

Output i want :

Total count on 6/15/2021 = 8

[ccc333ab]

Try this approach. This solution will:

• First will find the unique dates in your list
• Using the unique dates, find how many times it exists in the list 

FYI: My list has a date called "StartDate" that I am using in this example. 

1. Get your items, and then use select to just get the dates from your list

2. Then use a union statement to union the collection with itself to extract out only the unique dates. 

Expression: union(body('SelectDates'),body('SelectDates')) 

3. Now this Compose will have a list of all the unique dates. Loop through each of these and filter the original GetItems list for each of these dates. 

4. Now you can use length to count how many items of that particular date. You can put this into a variable, send an email, put in a list or whatever you are trying to do. I am just using a compose here to show you the result. 

Expression:

Count: length(body('Filter_array')) 

And running this produces:

(You can see multiple dates)

(Now unique)

And some of the final results:

 

[ccc333ab · Accepted solution]

Regarding your DM, I will answer here otherwise that defeats the purpose of this community. If someone else has this issue, then they can find it on here as well. 

So if you want to sum up another column of values per date, I have updated the above flow with the following features. 

1. Initialized a variable so that I can sum up the values during the loop. 

2. Because now we'll have to loop through all the unique dates and sum up the values in your column, I have added a new apply to each loop right after the filter array (using the Filter Array's BODY to loop through). 

And during this loop, I simply addup each value for that row....in my example I have a column named NumItems. 

Then I updated my compose statement to use the intSumValues variable instead of the count I did before. This will now show the total sum of that column by that particular unique date. (This is where you would email or whatever you are trying to with your flow). 

Note: You then also have to zero out the variable before you go and do this again for the next unique date). 

And because this was explained through two different flows, here is the whole flow shown at one time. 

And just to show you what I go, here is the run. For date 2021-06-06 I had 3 rows with the values of 2, 5 and 2. This was the result of the flow: