cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jamesantony Advocate I
Advocate I

Sort by date is not working - please help

Hello Experts,

 

I have 2 tables one to get hte data from SharePoint list and other to take only needed columns with grouping.

in the second table I need to sort it based on dates so that the line graph will show in the order of the dates..

 

right now it is sorting based on text.. but the column is already in date format. dont know where is the problem.

My expectation is to show the app usage by date for the last 10 days may be.. and I am trying to use the modified date to work around.. of you have any other suggestion also please let me know.

 

Thank you all.

 

please help.

 

First Table:

ClearCollect(UsageTrend, 'Lessons Learned Library'.Modified, 'Lessons Learned Library'.Test_Field); /*Get the values into a master Table*/
ClearCollect(UsageTrend, AddColumns('Lessons Learned Library', "Usage_Date", Text(Modified, DateTimeFormat.ShortDate))); /*Take only the date*/

 

Second Table:
ClearCollect(UsageCollect, AddColumns(GroupBy(UsageTrend, "Usage_Date", "Grouped"), "Nums", CountRows(Grouped))); 
ClearCollect(UsageCollect, AddColumns(UsageTrend, "Dateform", DateValue("Usage_Date", DateTimeFormat.ShortDate)));
ClearCollect(UsageCollect, SortByColumns(UsageCollect, "Usage_Date"))

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Meneghino Community Champion
Community Champion

Re: Sort by date is not working - please help

You are right.  Try this please:

/*First Table:*/
ClearCollect(UsageTrend, ShowColumns('Lessons Learned Library', "Modified", "Test_Field")); /*Get the values of two columns only into a master table*/
 
/*Second Table:*/
ClearCollect(UsageCollect, SortByColumns(AddColumns(GroupBy(
	AddColumns(UsageTrend, "MyDate", Date(Year(Modified), Month(Modified), Day(Modified))),
	"MyDate", "Grouped"), "Nums", CountRows(Grouped)), "Modified", Descending))

 

You can format the date as text when you have to display it, like this:

Text(MyDate, "mm/dd/yyyy")

 

View solution in original post

jamesantony Advocate I
Advocate I

Re: Sort by date is not working - please help

So kind of you @Meneghino

 

Great responses. I updated like this and it worked like charm. It was actually the text format that caused the issue. If I put Date format with long date, short date etc., it is not working. With this format"mm/dd/yyyy" it worked like a charm.

 

is there a way to get the data labels in the graph? alos is it possible to change the line color and thickness?

I see the item colorset but there is a lot... not sure whichone represents the line.

 

 

 

/*Colelction for Usage Trend Graph*/

ClearCollect(UsageTrend, ShowColumns('Lessons Learned Library', "Modified", "Test_Field")); /*Get the values into a master Table*/
ClearCollect(UsageTrend, AddColumns('Lessons Learned Library', "Usage_Date", Text(Modified, "[$-en-US]mm/dd/yyyy"))); /*Take only the date*/

ClearCollect(UsageCollect, SortByColumns(AddColumns(GroupBy(UsageTrend, "Usage_Date", "Grouped"), "Nums", CountRows(Grouped)),"Usage_Date", Ascending))

 

View solution in original post

8 REPLIES 8
Meneghino Community Champion
Community Champion

Re: Sort by date is not working - please help

Hello @jamesantony

I am not sure I follow your code.  The sequential steps should be nested instead of being in series.

Also, the first ClearCollect seems to be collecting two one-row tables one after the other.

You probably need something like this:

 

/*First Table:*/
ClearCollect(UsageTrend, ShowColumns('Lessons Learned Library', "Modified", "Test_Field")); /*Get the values of two columns only into a master table*/
 
/*Second Table:*/
ClearCollect(UsageCollect, SortByColumns(AddColumns(GroupBy(UsageTrend, "Modified", "Grouped"), "Nums", CountRows(Grouped)), "Modified"))

If you want to change the name of the "Modified" column you just nest a RenameColumns function.

jamesantony Advocate I
Advocate I

Re: Sort by date is not working - please help

@Meneghino thank you for your quick response.

 

I have a sharepoint list - "Library"

I cannot use the data as is for the line chart because I need to group the count based on Modified date. [which has time stamp also] but I need only Date.

So trying to create a temp collection.

First table I got the records from Sharepoint list. formatted the date column as needed and group the record count column created.

Second table - I need to sort the data in the date desending order.

 

Hope thsi explains my situation.. let me know if you need more details.

Meneghino Community Champion
Community Champion

Re: Sort by date is not working - please help

It does explain, thank you.

Does my suggestion improve the situation?

Do you understand where I see issues with your code?

jamesantony Advocate I
Advocate I

Re: Sort by date is not working - please help

It is not working yet.. 

First I need to format the date column to short date. mm/dd/yyyy [datetimevalue("Modified", ShortDate)] - where can I do this?

 

Usage_Trend is fine - it is working.

UsageCollect - not working yet..

Error - invalid argument type.

 

ClearCollect(UsageCollect, SortByColumns(AddColumns(GroupBy(UsageTrend, "Modified", "Grouped"), "Nums", CountRows(Grouped)), "Modified", Descending))

 

Highlighted
Meneghino Community Champion
Community Champion

Re: Sort by date is not working - please help

You are right.  Try this please:

/*First Table:*/
ClearCollect(UsageTrend, ShowColumns('Lessons Learned Library', "Modified", "Test_Field")); /*Get the values of two columns only into a master table*/
 
/*Second Table:*/
ClearCollect(UsageCollect, SortByColumns(AddColumns(GroupBy(
	AddColumns(UsageTrend, "MyDate", Date(Year(Modified), Month(Modified), Day(Modified))),
	"MyDate", "Grouped"), "Nums", CountRows(Grouped)), "Modified", Descending))

 

You can format the date as text when you have to display it, like this:

Text(MyDate, "mm/dd/yyyy")

 

View solution in original post

jamesantony Advocate I
Advocate I

Re: Sort by date is not working - please help

So kind of you @Meneghino

 

Great responses. I updated like this and it worked like charm. It was actually the text format that caused the issue. If I put Date format with long date, short date etc., it is not working. With this format"mm/dd/yyyy" it worked like a charm.

 

is there a way to get the data labels in the graph? alos is it possible to change the line color and thickness?

I see the item colorset but there is a lot... not sure whichone represents the line.

 

 

 

/*Colelction for Usage Trend Graph*/

ClearCollect(UsageTrend, ShowColumns('Lessons Learned Library', "Modified", "Test_Field")); /*Get the values into a master Table*/
ClearCollect(UsageTrend, AddColumns('Lessons Learned Library', "Usage_Date", Text(Modified, "[$-en-US]mm/dd/yyyy"))); /*Take only the date*/

ClearCollect(UsageCollect, SortByColumns(AddColumns(GroupBy(UsageTrend, "Usage_Date", "Grouped"), "Nums", CountRows(Grouped)),"Usage_Date", Ascending))

 

View solution in original post

Meneghino Community Champion
Community Champion

Re: Sort by date is not working - please help

Glad to help.  Have not worked with graphs for a long time so can't assist you on that one.

jamesantony Advocate I
Advocate I

Re: Sort by date is not working - please help

no worries. Thank you.

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Top Solution Authors
Top Kudoed Authors
Users online (9,784)