Need to show the amount of repeating dates in gallery

[tonysommer]

I have a gallery and in that gallery are dates. There are 28 items in total with some repeating. For example the date 5/28/2020 is displayed 3 times in the gallery. Other dates are only displayed once.

My formula is:

ClearCollect(
    Dates,
    Filter(
        IssueTracker,
        DateDiff(
            DateClosed,
            Now()
        ) < 30 && Status.Value = "Closed"
    )
)

Can anybody help with a formula to show the amount of times each date is shown? So for instance I would like to have the output of the formula look like the following:

5/28/2020 - Showed 3 times

5/26/2020 - Showed 1 time

5/20/2020 - Showed 2 times

5/15/2020 - Showed 1 time

etc...

[KrishnaV]

Hi @tonysommer,

You can use GroupBy function with date, see the below example and you can implement the same for your scenario:
Assume that this is you table/SPList:

ClearCollect( CityPopulations, 
    { City: "London",    Country: "United Kingdom", Population: 8615000}, 
    { City: "Berlin",    Country: "Germany",        Population: 3562000}, 
    { City: "Madrid",    Country: "Spain",          Population: 3165000}, 
    { City: "Rome",      Country: "Italy",          Population: 2874000}, 
    { City: "Paris",     Country: "France",         Population: 2273000}, 
    { City: "Hamburg",   Country: "Germany",        Population: 1760000}, 
    { City: "Barcelona", Country: "Spain",          Population: 1602000}, 
    { City: "Munich",    Country: "Germany",        Population: 1494000}, 
    { City: "Milan",     Country: "Italy",          Population: 1344000}
)

The data in the collection will be as below:

ClearCollect( CitiesByCountry, GroupBy( CityPopulations, "Country", "Cities" ) )

The result will be as below:

Similar to this you can do as below to get the count:

ClearCollect( CitiesByCountry, GroupBy(CityPopulations, "Country", "Cities"),countRows(GroupBy( CityPopulations, "Country", "Cities" )))

This will show the count of the entries in the collection.

I hope this resolved your issue if you see any challenge/need further help let me know I am always happy to help.

Regards,
Krishna
If this post helps give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

[eka24]

Try:

AddColumns(

GroupBy(IssueTracker,"DateClosed","CountDates"),

"Totals", CountRows(CountDates))

Inside one of the labels in the Gallery put:

"Showed:"&" "&ThisItem.Totals&" "&"Times"

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[tonysommer]

Will this the number of items of each date shown or just the total of the amount of records?

My collection ends up looking like the attached.

[eka24]

I dont know what you put in the Collection but this is what i did before sending (On Gallery Items):

[tonysommer]

The collections formula is:

ClearCollect(
    Records,
    Filter(
        IssueTracker,
        DateDiff(
            DateClosed,
            Now()
        ) < 30 && Status.Value = "Closed"
    )
)

When I implement your formula however as the Items property in the gallery I only get the result of 1 for each row.

I believe the issue may be that on the backend in the SharePoint list, it is an actual date/time field and not text representing a date.

[eka24 · Accepted solution]

The formula you have shown will not produces what you are requesting for, that is number of times for each date.

Can you show a screen shot of how the formula I gave when you used it. Do you have duplicate dates in your data for you to get

more than one count at a time.

Please a screen shot of the Formula I gave

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[tonysommer]

I have reproduced your test on my side just as you have it. However it only works when the date defined is text and not an actual date/time field from SharePoint. Is there anyway to count the items like I want when they are actual dates and not text?

The collection with dates is:

ClearCollect(
    testing,
    {
        DateClosed: "5/22/2020",
    },
    {
        DateClosed: "5/22/2020",
    },
    {
        DateClosed: "5/10/2020",
    },
    {
        DateClosed: "5/11/2020",
    }
)

The formula for the gallery is as follows:

AddColumns(
    GroupBy(
        testing,
        "DateClosed",
        "CountDates"
    ),
    "Totals",
    CountRows(CountDates)
)

This does produce the results I am looking for, however that has the dates as actual text. When the dates are from a sharepoint list and their data type is DATE/TIME it does not show the proper counts and instead only shows 1 for the count no matter if there are duplicate dates or not.

[eka24 · Accepted solution]

Create a collection Onvisible or OnStart of the App:

ClearCollect (ColIssueTracker, Addcolumns (IssueTracker,"NewDateClosed",Text(DateClosed,"dd-mmm-yyyy")))

Then change the formula on the Gallery to:

AddColumns(

GroupBy(ColIssueTracker,"NewDateClosed","CountDates"),

"Totals", CountRows(CountDates))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[tonysommer]

That did the trick! Thank you so much for your time and effort. Thank you everybody else for pointing me in the right direction!