cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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

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
Community Champion
Community Champion

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.

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

It does explain, thank you.

Does my suggestion improve the situation?

Do you understand where I see issues with your code?

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

 

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

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

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

no worries. Thank you.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (13,100)