Sort by date is not working - please help

[jamesantony]

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

[Meneghino]

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]

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

It does explain, thank you.

Does my suggestion improve the situation?

Do you understand where I see issues with your code?

[jamesantony]

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

[Meneghino · Accepted solution]

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

[jamesantony · Accepted solution]

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

[Meneghino]

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

[jamesantony]

no worries. Thank you.