cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Need to show the amount of repeating dates in gallery

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...

2 ACCEPTED SOLUTIONS

Accepted Solutions

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

Capture22.JPG

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

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.

View solution in original post

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.

 

View solution in original post

9 REPLIES 9
Super User III
Super User III

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:

Collection data for group by example1.png

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.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Proud to be a Super User!

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.
Super User III
Super User III

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.

 

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.

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

datesd.JPG

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.

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

Capture22.JPG

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

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.

View solution in original post

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.

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.

 

View solution in original post

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

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (61,944)